Date problem - AARRRGHHH!!

S

saraqpost

I can't believe there's no post on this, but I can't find it. I've
tried the solutions I could find (see below) and still get the error:
"The expression is typed incorrectly or it is too complex to be
evaluated. For example....Try simplifying...."

I have a date field, formatted as Date/Time, General Date, default
Value = Now().

I like having the date and the time - in case we need it.

I want to run a report on calls taken just for one day.



I have CallDate: CallDateAndTime in my query, with "criteria" as
Between ([Forms]![frmReports]![txtStartDate]) And ([Forms]!
[frmReports]![txtStartDate])+1

and have tried
([Forms]![frmReports]![txtStartDate]) with the field in the query as
CallDate: DateValue([CallDateAndTime])


and have tried
Query field: CallDate: CallDateAndTime
with a Where clause
Fix([CallDateAndTime])=([Forms]![frmReports]![txtStartDate]), and
displaying the field

I've tried putting in the actual dates (10/27/07) or Between 10/27/07
and 10/31/07 and I'm getting "Data type mismatch" as if the CallDate
field isn't a date format?

I can post the whole query if that is needed. I am STUCK! Please
help!!

thanks -
Sara
 
G

Guest

There shouldn't be problems with using:

DateValue([YourDateField]) = Forms!frmReports!txtStartDate

If your query is a crosstab (or feeds a crosstab) then you will need to
specify the data type of the form control.
 
S

saraqpost

There shouldn't be problems with using:

DateValue([YourDateField]) = Forms!frmReports!txtStartDate

If your query is a crosstab (or feeds a crosstab) then you will need to
specify the data type of the form control.

--
Duane Hookom
Microsoft Access MVP



[email protected] said:
I can't believe there's no post on this, but I can't find it. I've
tried the solutions I could find (see below) and still get the error:
"The expression is typed incorrectly or it is too complex to be
evaluated. For example....Try simplifying...."
Click to expand...
I have a date field, formatted as Date/Time, General Date, default
Value = Now().
Click to expand...
I like having the date and the time - in case we need it.
Click to expand...
I want to run a report on calls taken just for one day.
Click to expand...
I have CallDate: CallDateAndTime in my query, with "criteria" as
Between ([Forms]![frmReports]![txtStartDate]) And ([Forms]!
[frmReports]![txtStartDate])+1
Click to expand...
and have tried
([Forms]![frmReports]![txtStartDate]) with the field in the query as
CallDate: DateValue([CallDateAndTime])
Click to expand...
and have tried
Query field: CallDate: CallDateAndTime
with a Where clause
Fix([CallDateAndTime])=([Forms]![frmReports]![txtStartDate]), and
displaying the field
Click to expand...
I've tried putting in the actual dates (10/27/07) or Between 10/27/07
and 10/31/07 and I'm getting "Data type mismatch" as if the CallDate
field isn't a date format?
Click to expand...
I can post the whole query if that is needed. I am STUCK! Please
help!!
Click to expand...
thanks -
Sara- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

That's what I thought, but it doesn't work! Any ideas? I have pasted
the SQL below. I get results if I take out the parameter.

SELECT tblInitialCallInfo.CallerKey, [CallerFirstName] & " " &
[CallerLastName] AS Caller, tblInitialCallInfo.CallerAddress,
tlkpTowns_1.Town AS CallerTown, tblInitialCallInfo.CallerState,
tblInitialCallInfo.CallerZip, tblInitialCallInfo.CallerPhone,
tblInitialCallInfo.CallDateAndTime, DateValue([CallDateAndTime]) AS
CallDate, TimeValue([CallDateAndTime]) AS CallTime,
tlkpPhoneType.PhoneType, tblInitialCallInfo.CallerEmail,
tblInitialCallInfo.ClientKey, tlkpCallerType.CallerType,
tlkpRelationship.Relationship, [ReferralSourceFirstName] & " " &
[ReferralSourceLastName] AS ReferralSourceName,
tblInitialCallInfo.ReferralSourceTitle, tblAgencies.AgencyName AS
ReferralAgency, [StaffFirstName] & " " & [StaffLastName] AS Staff,
tblInitialCallInfo.CallerPresProblem, tlkpDisposition.Disposition,
tlkpClientCurrently.ClientCurrently, tblInitialCallInfo.DischargeDate
FROM (((((((tblInitialCallInfo LEFT JOIN tlkpPhoneType ON
tblInitialCallInfo.CallerPhoneTypeKey = tlkpPhoneType.PhoneTypeKey)
LEFT JOIN tlkpCallerType ON tblInitialCallInfo.CallerTypeKey =
tlkpCallerType.CallerTypeKey) LEFT JOIN tlkpRelationship ON
tblInitialCallInfo.RelationshipKey = tlkpRelationship.RelationshipKey)
LEFT JOIN tblAgencies ON tblInitialCallInfo.ReferralAgencyKey =
tblAgencies.AgencyKey) LEFT JOIN tblStaff ON
tblInitialCallInfo.StaffKey = tblStaff.StaffKey) LEFT JOIN
tlkpClientCurrently ON tblInitialCallInfo.ClientCurrentlyKey =
tlkpClientCurrently.ClientCurrentlyKey) LEFT JOIN tlkpDisposition ON
tblInitialCallInfo.DispositionKey = tlkpDisposition.DispositionKey)
LEFT JOIN tlkpTowns AS tlkpTowns_1 ON tblInitialCallInfo.CallerTownKey
= tlkpTowns_1.TownKey
WHERE (((DateValue([CallDateAndTime]))=[Forms]![frmReports]!
[txtStartDate]))
ORDER BY tblInitialCallInfo.CallDateAndTime;


Thanks!

Sara
 
J

John Spencer

Try declaring the parameter as follows to make sure you are getting a
DateTime value in the parameter.

PARAMETERS Forms]![frmReports]![txtStartDate] DateTime;
SELECT ...
FROM...
WHERE DateValue([CallDateAndTime])=
[Forms]![frmReports]![txtStartDate]
ORDER BY tblInitialCallInfo.CallDateAndTime;

One problem is that if CallDateAndTime is ever null or any value that can't
be interpreted as a date, the DateValue and TimeValue functions will
generate errors. So you might try
WHERE IIF(IsDate(CallDateAndTime,DateValue(CallDateAndTime),#1/1/1900#)) =
[Forms]![frmReports]![txtStartDate]

If callDateAndTime is going to be a valid date and time you could just use
the NZ function
WHERE DateValue(CallDateAndTime,#1/1/1900#) =
[Forms]![frmReports]![txtStartDate]

Another way to handle this is to use a date range
WHERE CallDateAndTime >= [Forms]![frmReports]![txtStartDate] and
CallDateTime < DateAdd("d",1,[Forms]![frmReports]![txtStartDate])

I'm not sure but you didn't include the # delimiters when you posted that
you entered hardcoded dates as a test. I believe you are aware that the
clause should read
WHERE CallDateAndTime BETWEEN #10/27/07# and #10/31/07#
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

There shouldn't be problems with using:

DateValue([YourDateField]) = Forms!frmReports!txtStartDate

If your query is a crosstab (or feeds a crosstab) then you will need to
specify the data type of the form control.

--
Duane Hookom
Microsoft Access MVP



[email protected] said:
I can't believe there's no post on this, but I can't find it. I've
tried the solutions I could find (see below) and still get the error:
"The expression is typed incorrectly or it is too complex to be
evaluated. For example....Try simplifying...."
Click to expand...
I have a date field, formatted as Date/Time, General Date, default
Value = Now().
Click to expand...
I like having the date and the time - in case we need it.
Click to expand...
I want to run a report on calls taken just for one day.
Click to expand...
I have CallDate: CallDateAndTime in my query, with "criteria" as
Between ([Forms]![frmReports]![txtStartDate]) And ([Forms]!
[frmReports]![txtStartDate])+1
Click to expand...
and have tried
([Forms]![frmReports]![txtStartDate]) with the field in the query as
CallDate: DateValue([CallDateAndTime])
Click to expand...
and have tried
Query field: CallDate: CallDateAndTime
with a Where clause
Fix([CallDateAndTime])=([Forms]![frmReports]![txtStartDate]), and
displaying the field
Click to expand...
I've tried putting in the actual dates (10/27/07) or Between 10/27/07
and 10/31/07 and I'm getting "Data type mismatch" as if the CallDate
field isn't a date format?
Click to expand...
I can post the whole query if that is needed. I am STUCK! Please
help!!
Click to expand...
thanks -
Sara- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

That's what I thought, but it doesn't work! Any ideas? I have pasted
the SQL below. I get results if I take out the parameter.

SELECT tblInitialCallInfo.CallerKey, [CallerFirstName] & " " &
[CallerLastName] AS Caller, tblInitialCallInfo.CallerAddress,
tlkpTowns_1.Town AS CallerTown, tblInitialCallInfo.CallerState,
tblInitialCallInfo.CallerZip, tblInitialCallInfo.CallerPhone,
tblInitialCallInfo.CallDateAndTime, DateValue([CallDateAndTime]) AS
CallDate, TimeValue([CallDateAndTime]) AS CallTime,
tlkpPhoneType.PhoneType, tblInitialCallInfo.CallerEmail,
tblInitialCallInfo.ClientKey, tlkpCallerType.CallerType,
tlkpRelationship.Relationship, [ReferralSourceFirstName] & " " &
[ReferralSourceLastName] AS ReferralSourceName,
tblInitialCallInfo.ReferralSourceTitle, tblAgencies.AgencyName AS
ReferralAgency, [StaffFirstName] & " " & [StaffLastName] AS Staff,
tblInitialCallInfo.CallerPresProblem, tlkpDisposition.Disposition,
tlkpClientCurrently.ClientCurrently, tblInitialCallInfo.DischargeDate
FROM (((((((tblInitialCallInfo LEFT JOIN tlkpPhoneType ON
tblInitialCallInfo.CallerPhoneTypeKey = tlkpPhoneType.PhoneTypeKey)
LEFT JOIN tlkpCallerType ON tblInitialCallInfo.CallerTypeKey =
tlkpCallerType.CallerTypeKey) LEFT JOIN tlkpRelationship ON
tblInitialCallInfo.RelationshipKey = tlkpRelationship.RelationshipKey)
LEFT JOIN tblAgencies ON tblInitialCallInfo.ReferralAgencyKey =
tblAgencies.AgencyKey) LEFT JOIN tblStaff ON
tblInitialCallInfo.StaffKey = tblStaff.StaffKey) LEFT JOIN
tlkpClientCurrently ON tblInitialCallInfo.ClientCurrentlyKey =
tlkpClientCurrently.ClientCurrentlyKey) LEFT JOIN tlkpDisposition ON
tblInitialCallInfo.DispositionKey = tlkpDisposition.DispositionKey)
LEFT JOIN tlkpTowns AS tlkpTowns_1 ON tblInitialCallInfo.CallerTownKey
= tlkpTowns_1.TownKey
WHERE (((DateValue([CallDateAndTime]))=[Forms]![frmReports]!
[txtStartDate]))
ORDER BY tblInitialCallInfo.CallDateAndTime;


Thanks!

Sara
Click to expand...
 
S

saraqpost

Try declaring the parameter as follows to make sure you are getting a
DateTime value in the parameter.

PARAMETERS Forms]![frmReports]![txtStartDate] DateTime;
SELECT ...
FROM...
WHERE DateValue([CallDateAndTime])=
[Forms]![frmReports]![txtStartDate]
ORDER BY tblInitialCallInfo.CallDateAndTime;

One problem is that if CallDateAndTime is ever null or any value that can't
be interpreted as a date, the DateValue and TimeValue functions will
generate errors. So you might try
WHERE IIF(IsDate(CallDateAndTime,DateValue(CallDateAndTime),#1/1/1900#)) =
[Forms]![frmReports]![txtStartDate]

If callDateAndTime is going to be a valid date and time you could just use
the NZ function
WHERE DateValue(CallDateAndTime,#1/1/1900#) =
[Forms]![frmReports]![txtStartDate]

Another way to handle this is to use a date range
WHERE CallDateAndTime >= [Forms]![frmReports]![txtStartDate] and
CallDateTime < DateAdd("d",1,[Forms]![frmReports]![txtStartDate])

I'm not sure but you didn't include the # delimiters when you posted that
you entered hardcoded dates as a test. I believe you are aware that the
clause should read
WHERE CallDateAndTime BETWEEN #10/27/07# and #10/31/07#
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




There shouldn't be problems with using:
DateValue([YourDateField]) = Forms!frmReports!txtStartDate
If your query is a crosstab (or feeds a crosstab) then you will need to
specify the data type of the form control.
--
Duane Hookom
Microsoft Access MVP
:
I can't believe there's no post on this, but I can't find it. I've
tried the solutions I could find (see below) and still get the error:
"The expression is typed incorrectly or it is too complex to be
evaluated. For example....Try simplifying...."
I have a date field, formatted as Date/Time, General Date, default
Value = Now().
I like having the date and the time - in case we need it.
I want to run a report on calls taken just for one day.
I have CallDate: CallDateAndTime in my query, with "criteria" as
Between ([Forms]![frmReports]![txtStartDate]) And ([Forms]!
[frmReports]![txtStartDate])+1
and have tried
([Forms]![frmReports]![txtStartDate]) with the field in the query as
CallDate: DateValue([CallDateAndTime])
and have tried
Query field: CallDate: CallDateAndTime
with a Where clause
Fix([CallDateAndTime])=([Forms]![frmReports]![txtStartDate]), and
displaying the field
I've tried putting in the actual dates (10/27/07) or Between 10/27/07
and 10/31/07 and I'm getting "Data type mismatch" as if the CallDate
field isn't a date format?
I can post the whole query if that is needed. I am STUCK! Please
help!!
thanks -
Sara- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
That's what I thought, but it doesn't work! Any ideas? I have pasted
the SQL below. I get results if I take out the parameter.
Click to expand...
SELECT tblInitialCallInfo.CallerKey, [CallerFirstName] & " " &
[CallerLastName] AS Caller, tblInitialCallInfo.CallerAddress,
tlkpTowns_1.Town AS CallerTown, tblInitialCallInfo.CallerState,
tblInitialCallInfo.CallerZip, tblInitialCallInfo.CallerPhone,
tblInitialCallInfo.CallDateAndTime, DateValue([CallDateAndTime]) AS
CallDate, TimeValue([CallDateAndTime]) AS CallTime,
tlkpPhoneType.PhoneType, tblInitialCallInfo.CallerEmail,
tblInitialCallInfo.ClientKey, tlkpCallerType.CallerType,
tlkpRelationship.Relationship, [ReferralSourceFirstName] & " " &
[ReferralSourceLastName] AS ReferralSourceName,
tblInitialCallInfo.ReferralSourceTitle, tblAgencies.AgencyName AS
ReferralAgency, [StaffFirstName] & " " & [StaffLastName] AS Staff,
tblInitialCallInfo.CallerPresProblem, tlkpDisposition.Disposition,
tlkpClientCurrently.ClientCurrently, tblInitialCallInfo.DischargeDate
FROM (((((((tblInitialCallInfo LEFT JOIN tlkpPhoneType ON
tblInitialCallInfo.CallerPhoneTypeKey = tlkpPhoneType.PhoneTypeKey)
LEFT JOIN tlkpCallerType ON tblInitialCallInfo.CallerTypeKey =
tlkpCallerType.CallerTypeKey) LEFT JOIN tlkpRelationship ON
tblInitialCallInfo.RelationshipKey = tlkpRelationship.RelationshipKey)
LEFT JOIN tblAgencies ON tblInitialCallInfo.ReferralAgencyKey =
tblAgencies.AgencyKey) LEFT JOIN tblStaff ON
tblInitialCallInfo.StaffKey = tblStaff.StaffKey) LEFT JOIN
tlkpClientCurrently ON tblInitialCallInfo.ClientCurrentlyKey =
tlkpClientCurrently.ClientCurrentlyKey) LEFT JOIN tlkpDisposition ON
tblInitialCallInfo.DispositionKey = tlkpDisposition.DispositionKey)
LEFT JOIN tlkpTowns AS tlkpTowns_1 ON tblInitialCallInfo.CallerTownKey
= tlkpTowns_1.TownKey
WHERE (((DateValue([CallDateAndTime]))=[Forms]![frmReports]!
[txtStartDate]))
ORDER BY tblInitialCallInfo.CallDateAndTime;
Thanks!
Click to expand...

Sara- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks! I found that some of the dates were wrong - the year was
207. I am not sure how that happened, but I am developing so this
isn't all production data. I also found that when I opened the form
and entered the date on the form it works. When I type in the date as
a parameter (requested when I run the query) it doesn't work.

It all works now and I learned a lot. I've copied all these notes
into a document so I can review and use in the future - I definitely
have more to learn about working with date and time fields.

Many thanks -
Sara
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can't get query to select date from form 2
Reference controls on a dialogue form as the parameters 1
Criteria not including today 4
Text Header displaying incorrect information 2
OpenReport Action was Cancelled when attempting to Preview a repor 1
Only Null 2
Can't Ge A Report To Accept Paraemters 3
Date Range Query Problem 20

Top