text & date criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

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#" & "'")
 
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?
 
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


Jeff Boyce said:
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>

JoeBo said:
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#" & "'")
Click to expand...
Click to expand...
 
JoeBo said:
Idate = Format(Idate, "dd\/mm\/yyyy")
Rcount = DCount("*", "tblcard_use", _
"[card] = '" & Icard & "' and " & _
"idate<> '" & "#sdate#" And "#edate#" & "'")
Click to expand...
Click to expand...
Jeff Boyce said:
I'm confused. What are sdate and edate?
Click to expand...
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.
Click to expand...


' 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
 
Back
Top