Date value filtering out 0 in day!!

[Dana809904]

Hello,
I am having problems when using a date value coming from text box in a sql
string. The date is in the format mm/dd/yyyy. If the particular day is
less than 10 (probably the month for that matter), it automatically filters
out that 0 and makes it mm/d/yyyy which disallows me to return any values in
my sql statement. It is even doing it when I use CDate(string). I am
wondering what I need to do to keep that 0 in the day (and month) if the case
may be.
Thanks in advance,
Dana S.

 

[Douglas J. Steele]

Unless you apply an explicit format, Access gets its date format from the
Regional Settings (on the Control Panel). Odds are that you've specified the
format as m/d/yyyy there.

However, I don't understand why that would prevent you from returning any
values in your SQL statement. What's your SQL statement look like?

 

[Dana809904]

Thanks for the info on the Regional Settings. I had no idea that it pulled
settings directly off the computer. My regional settings was set to m/d/yyyy
so fixing that should fix the problem. HOWEVER, my db frontend will
ultimately be used on multiple computers so it would definitely be an issue
unless every computer was configured correctly. So how do you apply an
explicit format as you said?
Also, the reason it was keeping me from returning any values was for a
particular query, the user set date was a required field for the query, and I
programmed it to set the date in the date field as mm/dd/yyyy, therefore it
would not find any matches if it was searching for mm/d/yyyy. Hope that
makes sense.
Oh yeah, FYI, my statement was as follows:
"Select * from Mailing WHERE Mailing.SeminarID_FK = " & Me.cmbSeminar.Value
& " AND Mailing.RecordSource_FK = " & Me.cmbRecord.Value & " AND Mailing.Date
= '" & Me.txtDate & "';" If you identify anything that could be corrected
please let me know.
Thank you very much for the assistance Doug.
~Dana

 

[Rick Brandt]

Thanks for the info on the Regional Settings. I had no idea that it pulled
settings directly off the computer. My regional settings was set to m/d/yyyy
so fixing that should fix the problem. HOWEVER, my db frontend will
ultimately be used on multiple computers so it would definitely be an issue
unless every computer was configured correctly. So how do you apply an
explicit format as you said?

It is only the "named" formats that follow the settings in control panel...

Short Date
Long Date
Currency
etc..

If you use a format string like "mm/dd/yyyy" then you will get that format
on all pcs regardless of regional settings.

 

[Dana809904]

If you use a format string like "mm/dd/yyyy" then you will get that format
on all pcs regardless of regional settings.

Could you give me an example of how to use the format string you pointed out?
Thanks
Dana

 

[Douglas J. Steele]

First of all, don't use Date as the name of a field in a table. Date is a
reserved word, and using reserved words can lead to all sorts of problems.
(If you absolutely cannot rename it, enclose the field name in square
brackets, like Mailing.[Date])

Assuming that the date field in your table (which I'll call DateSent) is a
Date field, as opposed to a text field, you need to use # characters to
delimit the date in the SQL.

Try the following:

"Select * from Mailing WHERE Mailing.SeminarID_FK = " & Me.cmbSeminar.Value
& " AND Mailing.RecordSource_FK = " & Me.cmbRecord.Value & " AND
Mailing.DateSent

= " & Format(Me.txtDate, "\#mm\/dd\/yyyy\#")

That's safer than the alternative, which would be:

"Select * from Mailing WHERE Mailing.SeminarID_FK = " & Me.cmbSeminar.Value
& " AND Mailing.RecordSource_FK = " & Me.cmbRecord.Value & " AND
Mailing.DateSent >= #" & Me.txtDate & "#"

 

[Dana809904]

Thanks for the info Doug. I totally spaced on the Date field being a
reserved word of course. Also, for simplicity's sake I have the field as
text, not a Date. I probably should change it over to a date I suppose. I am
unfamiliar with the Format syntax you used, but I'll convert the field to a
date, change the name, and see how it responds with that formatting.
Thanks,
Dana

First of all, don't use Date as the name of a field in a table. Date is a
reserved word, and using reserved words can lead to all sorts of problems.
(If you absolutely cannot rename it, enclose the field name in square
brackets, like Mailing.[Date])

Assuming that the date field in your table (which I'll call DateSent) is a
Date field, as opposed to a text field, you need to use # characters to
delimit the date in the SQL.

Try the following:

"Select * from Mailing WHERE Mailing.SeminarID_FK = " & Me.cmbSeminar.Value
& " AND Mailing.RecordSource_FK = " & Me.cmbRecord.Value & " AND
Mailing.DateSent

= " & Format(Me.txtDate, "\#mm\/dd\/yyyy\#")

That's safer than the alternative, which would be:

"Select * from Mailing WHERE Mailing.SeminarID_FK = " & Me.cmbSeminar.Value
& " AND Mailing.RecordSource_FK = " & Me.cmbRecord.Value & " AND
Mailing.DateSent >= #" & Me.txtDate & "#"



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks for the info on the Regional Settings. I had no idea that it pulled
settings directly off the computer. My regional settings was set to m/d/yyyy
so fixing that should fix the problem. HOWEVER, my db frontend will
ultimately be used on multiple computers so it would definitely be an issue
unless every computer was configured correctly. So how do you apply an
explicit format as you said?
Also, the reason it was keeping me from returning any values was for a
particular query, the user set date was a required field for the query, and I
programmed it to set the date in the date field as mm/dd/yyyy, therefore it
would not find any matches if it was searching for mm/d/yyyy. Hope that
makes sense.
Oh yeah, FYI, my statement was as follows:
"Select * from Mailing WHERE Mailing.SeminarID_FK = " & Me.cmbSeminar.Value
& " AND Mailing.RecordSource_FK = " & Me.cmbRecord.Value & " AND Mailing.Date
= '" & Me.txtDate & "';" If you identify anything that could be corrected
please let me know.
Thank you very much for the assistance Doug.
~Dana

 

[John Vinson]

Oh yeah, FYI, my statement was as follows:
"Select * from Mailing WHERE Mailing.SeminarID_FK = " & Me.cmbSeminar.Value
& " AND Mailing.RecordSource_FK = " & Me.cmbRecord.Value & " AND Mailing.Date
= '" & Me.txtDate & "';" If you identify anything that could be corrected
please let me know.

Is Mailing.Date a Text field, then? A Date/Time field would be safer
and would allow you to sort dates chronologically; Text will sort all
Januarys in any year first, then all Februarys, etc.

Try (if it's Text)

AND Mailing.Date = '" & Format$(Me.[txtDate], "mm/dd/yyyy") & "';"

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Rick Brandt]

Could you give me an example of how to use the format string you pointed out?
Thanks
Dana

In a table, query, form, or report you would select the field or control while
in design view and find the Format property in the property sheet. In that
property you enter "mm/dd/yyyy".

 

[John Vinson]

Also, for simplicity's sake I have the field as
text, not a Date.

As you're finding - choosing to store data in the wrong datatype leads
to complexity, not simplicity! :-{)

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Dana809904]

Thanks for the info John. I did change the date field to the date type, and
changed the name away from the reserved word. I used John's idea for the
format: Mailing.DateSent >= " & Format(Me.txtDate, "\#mm\/dd\/yyyy\#")
However I am not sure why he suggested >= instead of =, and the format of
\#mm\/dd\/yyyy\# is pretty foreign to me but it all appears to work just fine
so I guess if it ain't broke don't fix it! =P I would like to know how it
functions however.
Thanks again,
DanaS

Oh yeah, FYI, my statement was as follows:
"Select * from Mailing WHERE Mailing.SeminarID_FK = " & Me.cmbSeminar.Value
& " AND Mailing.RecordSource_FK = " & Me.cmbRecord.Value & " AND Mailing.Date
= '" & Me.txtDate & "';" If you identify anything that could be corrected
please let me know.

Is Mailing.Date a Text field, then? A Date/Time field would be safer
and would allow you to sort dates chronologically; Text will sort all
Januarys in any year first, then all Februarys, etc.

Try (if it's Text)

AND Mailing.Date = '" & Format$(Me.[txtDate], "mm/dd/yyyy") & "';"

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Thanks for the info John. I did change the date field to the date type, and
changed the name away from the reserved word. I used John's idea for the
format: Mailing.DateSent >= " & Format(Me.txtDate, "\#mm\/dd\/yyyy\#")
However I am not sure why he suggested >= instead of =, and the format of
\#mm\/dd\/yyyy\# is pretty foreign to me but it all appears to work just fine
so I guess if it ain't broke don't fix it! =P I would like to know how it
functions however.

Don't know why he used >= (greater than or equal) either - use
whichever is appropriate for what YOU want. If the DateSent does not
contain a time portion the = will select only those records on that
specific date.

The \ character is a "quote" character which means to put the next
character literally into the string, even if it's meaningful as a
Format character. Today's date would be converted to a literal
octothorpe character, then the month, day and year separated by
slashes, then another octothorpe: #10/31/2004#.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps