question

AMERSAID-7084 avatar image
1 Vote"
AMERSAID-7084 asked AMERSAID-7084 answered

query for items during a certain period

HI


There is a table showing the types of medicines and the serial for each type of medicine.
There is a column for the quantity received from the item and another for the quantity spent from the item.
There is a column for the date and the last boolean value to delete and hide the element by not setting true

database access 2010 code vb.net .

Table:
MEDICALCOME_TB

column
MEDICALCOME_CODE : Item Code
MEDICALCOME_SERIAL : Company serial
MEDICALCOME_NAME : Item name
MEDICALCOME_COM :Incoming quantity
MEDICALCOME_LEAVE :outgoing quantity
MEDICALCOME_DATE :DATE
MEDICALCOME_CHECK : Checking a boolean value to use by deleting and archiving

I want to sum the period between two dates DateTimePicker - for the total incoming - and outgoing - and the difference between the incoming and outgoing remaining:

Is it possible to inquire within a month? And clarification of the details of the quantities disbursed for four weeks for the same month

I used the following code to search and group between two different date periods. Nothing appears

Blockquote

Dim A = Format(DATETIM_START.Value, "yyyy/dd/MM")

Dim B = Format(DATETIM_END.Value, "yyyy/dd/MM")

Dim DataAdapter As New OleDbDataAdapter("Select MEDICALCOME_TB.MEDICALCOME_CODE, MEDICALCOME_TB.MEDICALCOME_SERIAL, Sum(MEDICALCOME_TB.MEDICALCOME_COM) As SumOfMEDICALCOME_COM, Sum(MEDICALCOME_TB.MEDICALCOME_LEAVE) As SumOfMEDICALCOME_LEAVE
From MEDICALCOME_TB
Where (((MEDICALCOME_TB.MEDICALCOME_CHECK) = True) And ((MEDICALCOME_TB.MEDICALCOME_DATE)between #" & B & "# And #" & A & "#))
Group By MEDICALCOME_TB.MEDICALCOME_CODE, MEDICALCOME_TB.MEDICALCOME_SERIAL ;", con)




dotnet-visual-basic
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AMERSAID-7084 ,
Can you get some values after removing 'And ((MEDICALCOME_TB.MEDICALCOME_DATE)between #" & B & "# And #" & A & "#))' ?
Could you provide some data about 'MEDICALCOME_DATE' in the database? It will help us analyze your problem and make a test.
We are waiting for your update.

1 Vote 1 ·

HI
XingyuZhao

The query works when canceling the search condition between two dates

But I need to display the date during the month - and sum the quantities disbursed in the first week - and the second, third and fourth week of the same month.

1 Vote 1 ·
XingyuZhao-MSFT avatar image
1 Vote"
XingyuZhao-MSFT answered

Hi @AMERSAID-7084 ,
Change 'yyyy/dd/MM' to 'yyyy/MM/dd':

 Dim A = Format(DATETIM_START.Value, "yyyy/MM/dd")    
 Dim B = Format(DATETIM_END.Value, "yyyy/MM/dd")

Hope it could be helpful.

Best Regards,
Xingyu Zhao


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AMERSAID-7084 avatar image
0 Votes"
AMERSAID-7084 answered

hi

The query is working now

How to query the quantities disbursed for each week for four weeks within a month determined by the time tool??

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.