Form/Date Query

  • Thread starter Thread starter Wayne Morgan
  • Start date Start date
W

Wayne Morgan

Access has trouble interpreting dates in parameters. It doesn't pick up on
the data type. Placing date delimiters, #, around the date will also give an
error. To make it work, specify the parameter's data type first.

PARAMETERS [forms]![checkregister]![begdate] DateTime;
SELECT CheckRegisterPre.EntryBlankID, CheckRegisterPre.MoneyID,
CheckRegisterPre.Amount, CheckRegisterPre.Note, CheckRegisterPre.entrydate
FROM CheckRegisterPre
where checkregisterpre.entrydate = [forms]![checkregister]![begdate]
 
PS,

You need to set the clock on your computer. I'm answering these before you
sent the message.
 
I am trying to get the following query to work with my form

SELECT CheckRegisterPre.EntryBlankID, CheckRegisterPre.MoneyID,
CheckRegisterPre.Amount, CheckRegisterPre.Note, CheckRegisterPre.entrydate
FROM CheckRegisterPre
where checkregisterpre.entrydate = [forms]![checkregister]![begdate]

The CheckRegisterPre is a query. The entrydate field is a datevalue() of a
date/time field from a table. The begdate is a combo box with the dates in
it populated by another query that gets the datevalue() of the date/time
field. When I run this I get the following error

"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables"

Any ideas?
 
Wayne,

I tried this and got the same error message.

Dale
Wayne Morgan said:
Access has trouble interpreting dates in parameters. It doesn't pick up on
the data type. Placing date delimiters, #, around the date will also give
an error. To make it work, specify the parameter's data type first.

PARAMETERS [forms]![checkregister]![begdate] DateTime;
SELECT CheckRegisterPre.EntryBlankID, CheckRegisterPre.MoneyID,
CheckRegisterPre.Amount, CheckRegisterPre.Note, CheckRegisterPre.entrydate
FROM CheckRegisterPre
where checkregisterpre.entrydate = [forms]![checkregister]![begdate]

--
Wayne Morgan
MS Access MVP


Dale and Sandi Brown said:
I am trying to get the following query to work with my form

SELECT CheckRegisterPre.EntryBlankID, CheckRegisterPre.MoneyID,
CheckRegisterPre.Amount, CheckRegisterPre.Note,
CheckRegisterPre.entrydate
FROM CheckRegisterPre
where checkregisterpre.entrydate = [forms]![checkregister]![begdate]

The CheckRegisterPre is a query. The entrydate field is a datevalue() of
a
date/time field from a table. The begdate is a combo box with the dates
in
it populated by another query that gets the datevalue() of the date/time
field. When I run this I get the following error

"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables"

Any ideas?
Click to expand...
Click to expand...
 
In the checkregisterpre query, instead of DateValue(), try

CDate(Format([FieldName], "Short Date"))

If that doesn't work, since you're looking for a value that is equal, you
don't have to worry about the date being interpreted correctly. In which
case, try modifying the WHERE clause in the current query:

WHERE Format([checkregisterpre].[entrydate], "Short Date") =
Format([forms]![checkregister]![begdate], "Short Date")

This will do a text comparison, but since you're only after "equal", it
should work.
 
Back
Top