Solved Date between (1 Viewer)

theinviter

Registered User.
Local time
Today, 15:56
Joined
Aug 14, 2014
Messages
237
..
..
..
..
..
.
..
..
..
..
..
.
.
..


.
..
..
...
.
..
..
..
..
..


..
.
.
..


.
..
..
...
.
..
..
..
..
..
..
..
..
..
..
.
.
..


.
..
..
...
.
..
..
..
..
..

..
..
..
..
..
..
..
..
.
.
..


.
..
..
...
.
..
..
..
..
..

..
..
..
..
..
.
.
..


.
..
..
...
.
..
..
..
..
..
..
..
..
..
..
.
.
..


.
..
..
...
.
..
..
..
..
.
..
..
..
..
..
.
.
..


.
..
..
...
.
..
..
..
..
..

..
..
..
..
..
.
.
..


.
..
..
...
.
..
..
..
..
..





.
..
.
.
..


.
..
..
...
.
..
..
..
..
..

..
..
..
..
..
.
.
..


.
..
..
...
.
..
..
..
..
..



.
..
..
.
.
..


.
..
..
...
.
..
..
..
..
..



Dear guys;
need you help in the code below,
wanna get the data between 01/01/2022 to 12/31/2022
how to modify the code
Nz(DSum("[Link value]","[MRN]","[Item Code] ='" & [Item Code1] & "'AND [Location - Clinics Code] =" & [Location_Code] & " AND [Date_] BETWEEN # 1/1/2022#AND #12/31/2022#),0)
got error with above code
thanks
 
Last edited by a moderator:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:56
Joined
Jul 9, 2003
Messages
16,245
Oh! I see! You are the one who refuses to add code tags.

I withdraw my assistance....
 
Last edited:

cheekybuddha

AWF VIP
Local time
Today, 22:56
Joined
Jul 21, 2014
Messages
2,237
Easier to see if you use code tags!
Code:
Nz(DSum("[Link value]","[MRN]","[Item Code] ='" & [Item Code1] & "'AND [Location - Clinics Code] =" & [Location_Code] & " AND [Date_] BETWEEN # 1/1/2022#AND #12/31/2022#),0)

Try adding a space before 'AND' :
Code:
& [Item Code1] & "' AND

Also here:
Code:
BETWEEN #1/1/2022# AND #12/31/2022#
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:56
Joined
May 7, 2009
Messages
19,169
... AND Year([Date_]) = 2022
 

cheekybuddha

AWF VIP
Local time
Today, 22:56
Joined
Jul 21, 2014
Messages
2,237
... AND Year([Date_]) = 2022
This makes semantic sense, but if field Date_ is indexed then the index will not be used in the filtering and a full table scan will be required. Probably won't have a visible effect unless MRN is a large table.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:56
Joined
May 7, 2009
Messages
19,169
tried it will 1 million record and it shows the record instantly.
 

theinviter

Registered User.
Local time
Today, 15:56
Joined
Aug 14, 2014
Messages
237
i tried this but got error :
=DSum("[Link Value]","[MRN]","[Item Code]='" & [Item Code] & "' AND [Location - Clinics Code] =" & [Location_Code] And Year([Date_])=2022)

this appear in the field "#Name?"
 

theinviter

Registered User.
Local time
Today, 15:56
Joined
Aug 14, 2014
Messages
237
i tried this but got error :
=DSum("[Link Value]","[MRN]","[Item Code]='" & [Item Code] & "' AND [Location - Clinics Code] =" & [Location_Code] And Year([Date_])=2022)

this appear in the field "#Name?"
when i use the code without date , work great but when i add date criteria get above error .
 

cheekybuddha

AWF VIP
Local time
Today, 22:56
Joined
Jul 21, 2014
Messages
2,237
Are any of the dates Null?

What is the exact error you get?

Is this in VBA code or as an expressing on a query?
 

theinviter

Registered User.
Local time
Today, 15:56
Joined
Aug 14, 2014
Messages
237
Are any of the dates Null?

What is the exact error you get?

Is this in VBA code or as an expressing on a query?
Are any of the dates Null?
yes , as it read from table
What is the exact error you get?
"#Name?" , when i add date to code, but without date i get the total number.
Is this in VBA code or as an expressing on a query? no the code i made on unbound text field in form.
 

theinviter

Registered User.
Local time
Today, 15:56
Joined
Aug 14, 2014
Messages
237
Easier to see if you use code tags!
Code:
Nz(DSum("[Link value]","[MRN]","[Item Code] ='" & [Item Code1] & "'AND [Location - Clinics Code] =" & [Location_Code] & " AND [Date_] BETWEEN # 1/1/2022#AND #12/31/2022#),0)

Try adding a space before 'AND' :
Code:
& [Item Code1] & "' AND

Also here:
Code:
BETWEEN #1/1/2022# AND #12/31/2022#
i tried above but got this error as in attached picture.
 

Attachments

  • Untitled1.png
    Untitled1.png
    6.9 KB · Views: 182

cheekybuddha

AWF VIP
Local time
Today, 22:56
Joined
Jul 21, 2014
Messages
2,237
Try:
Code:
Nz(DSum("[Link value]","[MRN]","[Item Code] ='" & [Item Code1] & "' AND [Location - Clinics Code] =" & [Location_Code] & " AND ([Date_] BETWEEN #1/1/2022# AND #12/31/2022#) OR [_Date] IS NULL)",0)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:56
Joined
May 7, 2009
Messages
19,169
Nz(DSum("[Link value]", "[MRN]", "[Item Code] ='" & [Item Code1] & "' AND [Location - Clinics Code] = " & [Location_Code] & " AND Year([Date_]) = 2022"), 0)
 

Users who are viewing this thread

Top Bottom