Re: Date value filtering out 0 in day!!

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 10/05/04 

Date: Tue, 5 Oct 2004 15:31:35 -0400

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)
"Dana809904" <dsimmelink99.NOspam@hotmail.com> wrote in message
news:0013E1A3-2573-46DA-949B-7B6691F5271B@microsoft.com...
> 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
>
> "Douglas J. Steele" wrote:
>
> > 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)
> >
> >
> > "Dana809904" <dsimmelink99.NOspam@hotmail.com> wrote in message
> > news:03F24A9A-4179-4970-BBCF-25C4AE9150FF@microsoft.com...
> > > 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.
> >
> >
> >

Relevant Pages

  • Re: Date value filtering out 0 in day!!
    ... Unless you apply an explicit format, Access gets its date format from the ... Regional Settings (on the Control Panel). ... What's your SQL statement look like? ...
    (microsoft.public.access.modulesdaovba)
  • Re: NumberFormat does not change the label format
    ... In fact nothing in the regional settings seems to have any affect on ... the format of the graph dates. ... With axis type set to chAxisGroupingTypeNone, ...
    (microsoft.public.office.developer.web.components)
  • Re: Format an Excel Column in the windows short date format.
    ... Excel Internation properties lead me to construct: ... date format string I build from the excel properties to excel via the ... It works for most regional settings but not Norwegian. ...
    (microsoft.public.excel.programming)
  • Re: Currency formatted fields and region settings
    ... One that involves VBA and that would set the fields format based on the ... local machine regional settings whenever a document is opened. ... Dim myThouSep As Variant ... Set myFormfield = ActiveDocument.Content.FormFields ...
    (microsoft.public.word.vba.general)
  • Re: Cant use dd/mm/yyyy format
    ... mm/dd/yyyy format in SQL statements. ... > I have since found that I cannot use the dd/mm/yyyy date format, so I am now> wondering if I can use an SQL statement to apply criteria to open a form to> specific records. ... > Dim stLinkCriteria As String ...
    (microsoft.public.access.formscoding)