Problem referencing the form that a Dcount and If...Then...Else statement are place in

[jstaggs39]

I want to create a Dcount and an If...Then...Else statement to count
the number of records in a table based on the date that is entered to
run the form. The If....Else statment comes in because if the amount of
records for a particular date is positive, i want the form to stop
running, if there are no records that contain the date in question,
then it should continue to run the form. The problem i'm having is
referencing the date that is entered in the form in order to execute it
in the first place.
To better explain, the execution of the frmDlgDates requires a
"FromDate" and "ToDate" to be entered. The form then runs the queries
which then append the data to tables. The tables will contain the date
that was entered as the "ToDate". for example:

"FromDate" : 01/04/2005
"ToDate" : 30/04/2005

qry runs and populates a table called [tblQReturns]. the table now
contains x number of records with the date 30/04/2005.

the code i think will look something like (but the problem i'm having
is in referencing the "ToDate" that is entered in the form in the first
place. Also the dates I enter as From and To will be different each
time, so I can't build the expression with the exact date i'm looking
for):

If (Dcount("[Date]","tblQReturns","[Date] >0" &
Forms!frmdlgDates!ToDate) Then
Exit Sub

ElseIf (Dcount(("[Date]","tblQReturns","[Date] =0" &
Forms!frmdlgDates!ToDate) Then
DoCmd.OpenQuery "qryQReturns", acNormal, acEdit

End If
End Sub

is it a problem that the form i'm referencing is the form i'm trying to
execute? If anyone could help me with this problem I would greatly
appreciate it.

[BillCo]

I didnt follow excatly what you are doing, but this syntax is what you
are looking for. Jet requires dates to be delimited with # and the
format to be in month-day-year:

Dcount("[Date]","tblQReturns","[Date] > #" &
format(Forms!frmdlgDates!ToDate,"mm/dd/yyyy") & "#")

[jstaggs39]

thanks for the tip.
i ran this expression and it worked, but i'm not exactly sure if it
worked the way i want it to. what i'm looking for the expression to do
is:
"if the date field in the QReturns table contains >0 number of records
for that date we're running in the form, then exit the sub (b/c this
would mean that this date has previously been ran). if the date field
in the table =0 number of records for that date we're running the form
for then go ahead and run the query."
does this help clear up what i'm trying to accomplish and does it
change the expression any? thanks again for your help.

[BillCo]

try this :

if not isnull(Dcount("[Date]","tblQReturns","[Date] > #" &
format(Forms!frmdlgDates!ToDate,"mm/dd/yyyy") & "#") ) then
exit function
else
'continue processing

[jstaggs39]

this expression works if the dates have already been entered, but if
running the form with a new date, a "enter parameter values" screen
comes up asking again for the "to" date and the "from" date. when i
enter the dates again i get either an error message stating "overflow"
or "data type mismatch in criteria expression" depending on if i use a
backslash when typing in the date or not. any suggestions?
thanks again for the help.

[BillCo]

you were sending the dcount query a null value. make sure there is a
value in the form before you call the dcount:

if not isnull(forms!frmdlgDates!toDate) then
if not isnull(Dcount("[Date]","tblQReturns","[Date] > #" & _
format(Forms!frmdlgDates!ToDate,"mm/dd/yyyy") & "#") ) then
exit function
endif
endif

[jstaggs39]

nevermind. i got the expression to work correctly. thanks for all the
help.