Select Value as DateTime in Query

  • Thread starter Thread starter jwgoerlich
  • Start date Start date
J

jwgoerlich

Hello,

I am dynamically building a DateTime value in a query, like so:

CDate(Month([DateAlert]) & "-" & Day([DateAlert]) & "-" & Year(Now()))
AS DateSerial

My problem is that the resulting field is a string. Is there a way to
force a field to be a DateTime type?

Thanks in advance,

J Wolfgang Goerlich
 
First, the bad news. Do not use DateSerial as the alias name. DateSerial is
a reserved word in ACCESS because it's the name of a built-in VBA function.
See these articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763


Now the good news. DateSerial just happens to do exactly what you want! Try
this:

DateSerial(Year(Now()), Month([DateAlert]), Day([DateAlert])) AS TheDate

This will be a date/time data type and you can then format it however you
wish for display.
 
DateSerial is a function name, so try using that function and renaming your
alias:
DateSerial(Year(Date()), Month([DateAlert]), Day([DateAlert])) AS MyDate
 
Do not use DateSerial as the alias name. DateSerial is a reserved
word in ACCESS because it's the name of a built-in VBA function.
Click to expand...

Ah ha! That explains it. Makes me wonder why I picked that as name,
maybe some fragment of memory. Much obliged for the links.

I still have a problem using:
DateSerial(Year(Now()), Month([DateAlert]), Day([DateAlert])) AS
DateReport

The problem is a data type mismatch when I attempt to filter the
results. This, for example, works:
(DateAlert.DateAlert)=#1/12/2009#

This fails with "Data type mismatch in criteria expression":
DateSerial(Year(Now()),Month([DateAlert]),Day([DateAlert])))=#1/12/2005#

Any ideas?

J Wolfgang Goerlich
 
Typecast it:
CVDate(DateSerial(Year(Date()), Month([DateAlert]), Day([DateAlert])))
AS MyDate

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Do not use DateSerial as the alias name. DateSerial is a reserved
word in ACCESS because it's the name of a built-in VBA function.
Click to expand...

Ah ha! That explains it. Makes me wonder why I picked that as name,
maybe some fragment of memory. Much obliged for the links.

I still have a problem using:
DateSerial(Year(Now()), Month([DateAlert]), Day([DateAlert])) AS
DateReport

The problem is a data type mismatch when I attempt to filter the
results. This, for example, works:
(DateAlert.DateAlert)=#1/12/2009#

This fails with "Data type mismatch in criteria expression":
DateSerial(Year(Now()),Month([DateAlert]),Day([DateAlert])))=#1/12/2005#

Any ideas?

J Wolfgang Goerlich
Click to expand...
 
Thank you. Yes, the value needs to be typecast. Both CDate and CVDate
do not work, however.

J Wolfgang Goerlich
 
Wolfgang, I have never seen an Access query into an Access (JET) table where
a calculation typecast by CVDate() was not recognised as a date.

When you view the output of this query as a datasheet, does the field left
align (like text), or right-align (like a number or date)?

Do you want to post the entire SQL statement?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you. Yes, the value needs to be typecast. Both CDate and CVDate
do not work, however.

J Wolfgang Goerlich

Allen said:
Typecast it:
CVDate(DateSerial(Year(Date()), Month([DateAlert]),
Day([DateAlert])))
AS MyDate
Click to expand...
Click to expand...
 
Allen said:
Typecast it:
CVDate(DateSerial(Year(Date()), Month([DateAlert]), Day([DateAlert])))
AS MyDate
Click to expand...

Why cast? I thought DATESERIAL returns a DATETIME anyhow e.g.

SELECT TYPENAME(DATESERIAL(2009, 1, 12)),
DATESERIAL(2009, 1, 12) = #1/12/2009#
 
When you view the output of this query as a datasheet, does the field left
align (like text), or right-align (like a number or date)?
Click to expand...

The field is right-aligned and for all practical purposes appears to be
a date. The problem only occurrs when I filter the results.
Do you want to post the entire SQL statement?
Click to expand...

Sure. Here it is:

SELECT
DateAlert.DateAlert,
[Person].[FirstName] & IIf([Person].[MiddleName] Is Not Null," " &
[Person].[MiddleName] & " "," ") & [Person].[LastName] AS Person,
DateAlert.Comment,
CVDate(DateSerial(Year(Date()),Month([DateAlert]),Day([DateAlert])))
AS DateReport
FROM
(DateAlert INNER JOIN Contact ON DateAlert.KeyContact =
Contact.KeyContact)
INNER JOIN
Person ON Contact.KeyContact = Person.KeyContact
WHERE
(((DateAlert.DateAlert) Is Not Null) AND
((DateAlert.Alert)=1) AND
((Contact.Enabled)=1) AND
((Person.KeyPersonType)=1));

The purpose for this is to get a list of anniversaries, birthdays, and
other date alerts from a given time period (e.g. today, this week, this
month). I have a report that compares DateReport to a given date range
and lists the results.

J Wolfgang Goerlich
 
The query looks fine to me, and it is presenting as if JET understands the
type correctly. The only slightly ususual feature is a table and a field
that have the same name. You might need to be sure that Name AutoCorrect is
turned off, so Access doesn't get confused about that.

You say if fails when you filter it: How are you doing that? Are you
entering a literal value in the Criteria row in place of:
Is Not Null
or are you entering something like:
[Forms].[Form1].[Text0]



--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

When you view the output of this query as a datasheet, does the field
left
align (like text), or right-align (like a number or date)?
Click to expand...

The field is right-aligned and for all practical purposes appears to be
a date. The problem only occurrs when I filter the results.
Do you want to post the entire SQL statement?
Click to expand...

Sure. Here it is:

SELECT
DateAlert.DateAlert,
[Person].[FirstName] & IIf([Person].[MiddleName] Is Not Null," " &
[Person].[MiddleName] & " "," ") & [Person].[LastName] AS Person,
DateAlert.Comment,
CVDate(DateSerial(Year(Date()),Month([DateAlert]),Day([DateAlert])))
AS DateReport
FROM
(DateAlert INNER JOIN Contact ON DateAlert.KeyContact =
Contact.KeyContact)
INNER JOIN
Person ON Contact.KeyContact = Person.KeyContact
WHERE
(((DateAlert.DateAlert) Is Not Null) AND
((DateAlert.Alert)=1) AND
((Contact.Enabled)=1) AND
((Person.KeyPersonType)=1));

The purpose for this is to get a list of anniversaries, birthdays, and
other date alerts from a given time period (e.g. today, this week, this
month). I have a report that compares DateReport to a given date range
and lists the results.

J Wolfgang Goerlich
Click to expand...
 
You say if fails when you filter it: How are you doing that?

Programmatically, from a report open event.

Me.FilterOn = False
Me.Filter = "DateReport=#" & DateStart & "#"
Me.FilterOn = True

I can duplicate this on the original query by putting date value in the
Criteria row. Note that using DateAlert, which is a static valid
DateTime, works in both these scenarios.

Thanks for looking at this, appreciate the help.

J Wolfgang Goerlich
 
Allen said:
The query looks fine to me, and it is presenting as if JET understands the
type correctly.
Click to expand...

Got it! I think Access is pre-fetching the data and building the
DataReport alias, then removing the rows with Null DateAlert values.

As a work-around, I added a Update query to disable all Null DateAlert
rows _before_ previewing the report. My code is as follows:

Dim sql As String
Dim rep As String

DoCmd.SetWarnings False

sql = "Update DateAlert Set Alert=0 Where DateAlert Is Null;"
rep = "What is happening"

DoCmd.RunSQL sql
DoCmd.OpenReport rep, acViewPreview, , , , sArgument

DoCmd.SetWarnings False

This was a strange problem for me. Greatly appreciate all the help!

J Wolfgang Goerlich
 
Back
Top