text & date criteria

[JoeBo]

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