text & date criteria

[Guest]

Can someone help me to clean up my criteria string, I need to count the
existence of cards that have a date span: sdate to edate. Any help
appreciated.

Idate = InputBox("What is the start date for this card use", conAppName)
Idate = Format(Idate, "dd\/mm\/yyyy")
Rcount = DCount("*", "tblcard_use", "[card] = '" & Icard & "' and idate
<> '" & "#sdate#" And "#edate#" & "'")

 

[Jeff Boyce]

Joe

I'm confused. What are sdate and edate? If these are fields in your table,
wouldn't you want to look for Idate values related to them, rather than the
other way around?

 

[Guest]

Hello Jeff

Tblcard_use is a table which contains card use history, with fields:

card string field
sdate date field
edate date field

sdate & edate represent the start date and end date for the period the card
was used previously. I want dcount to count where Idate falls between sdate
and edate.
I hope this clarifies my code.
Thanks

Joe

I'm confused. What are sdate and edate? If these are fields in your table,
wouldn't you want to look for Idate values related to them, rather than the
other way around?

--
Good luck

Jeff Boyce
<Access MVP>

Can someone help me to clean up my criteria string, I need to count the
existence of cards that have a date span: sdate to edate. Any help
appreciated.

Idate = InputBox("What is the start date for this card use", conAppName)
Idate = Format(Idate, "dd\/mm\/yyyy")
Rcount = DCount("*", "tblcard_use", "[card] = '" & Icard & "' and idate
<> '" & "#sdate#" And "#edate#" & "'")

 

[Tim Ferguson]

Idate = Format(Idate, "dd\/mm\/yyyy")
Rcount = DCount("*", "tblcard_use", _
"[card] = '" & Icard & "' and " & _
"idate<> '" & "#sdate#" And "#edate#" & "'")

I'm confused. What are sdate and edate?

Hello Jeff

Tblcard_use is a table which contains card use history, with fields:

card string field
sdate date field
edate date field

sdate & edate represent the start date and end date for the period
the card was used previously. I want dcount to count where Idate
falls between sdate and edate.

' make sure you use a jet-legal date format
const c_jetformat = "\#yyyy\-mm\-dd\#"

' put the criterion together carefully
strCriterion = "SDate < " & format(IDate, c_jetformat)
strCriterion = strCriterion & " and "
strCriterion = strCriterion & format(IDate, c_jetformat) & " < EDate"

' this is the only way to see what is going on when you
' are constructing complicated SQL commands
debug.print strCriterion

' okay, now get the value
dwNumCards = DCount("Card", "tblcard_use", strCriterion)


Eezy peezy...


Tim F