Date value filtering out 0 in day!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dana809904 - 05 Oct 2004 18:55 GMT

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.

Reply to this Message

Douglas J. Steele - 05 Oct 2004 19:09 GMT

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

> Hello,
> I am having problems when using a date value coming from text box in a sql
[quoted text clipped - 6 lines]
> Thanks in advance,
> Dana S.

Reply to this Message

Dana809904 - 05 Oct 2004 19:37 GMT

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

> 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
[quoted text clipped - 21 lines]
> > Thanks in advance,
> > Dana S.

Reply to this Message

Rick Brandt - 05 Oct 2004 19:51 GMT

> 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.

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Dana809904 - 05 Oct 2004 20:19 GMT

> 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

Reply to this Message

Rick Brandt - 06 Oct 2004 00:00 GMT

>> 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

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".

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Douglas J. Steele - 05 Oct 2004 20:31 GMT

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
http://I.Am/DougSteele
(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
[quoted text clipped - 40 lines]
> > > Thanks in advance,
> > > Dana S.

Reply to this Message

Dana809904 - 05 Oct 2004 21:37 GMT

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.
[quoted text clipped - 81 lines]
> > > > Thanks in advance,
> > > > Dana S.

Reply to this Message

John Vinson - 06 Oct 2004 02:40 GMT

>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

Reply to this Message

John Vinson - 05 Oct 2004 21:42 GMT

>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

Reply to this Message

Dana809904 - 06 Oct 2004 17:19 GMT

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
[quoted text clipped - 14 lines]
> Tuesday 11am EDT - Thursday 3:30pm EDT
> http://community.compuserve.com/msdevapps

Reply to this Message

John Vinson - 31 Oct 2004 22:24 GMT

>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
[quoted text clipped - 3 lines]
>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

Reply to this Message