Re: Syntax error

John

Thanks - That has solved the problem.

Thank you for all who have been helping me.

Paul

"John Spencer (MVP)" wrote:

> DATES in SQL statement must be in the format mm-dd-yyyy or the format
> yyyy-mm-dd. Try
>
> "tblMMD.DateOfMeeting >= " & Format(Me!txtFrom,"\#yyyy\/mm\/dd\#") &
> " AND tblMMD.DateOfMeeting <= " & Format(Me!txtTo,"\#yyyy\/mm\/dd\#")
>
> Your dates of 1/4/2005 to 30/4/2005 are being interpreted as Jan 4, 2005 to
> April 30, 2005. This is because Access attempts to rectify the "Error" you've
> made in the date entry (mm/dd/yyyy).
>
> For 1/4/2005 there is now problem as 1 is a valid month number and 4 is a valid
> day number.
> For 30/4/2005 -
>
> 30 is an invalid month number, so lets see if it is a valid day. Switch that
> with the proposed day number if the proposed day number would be a valid month
> number. yep that works, so we now have 4/30/2005.
>
>
> Paul wrote:
> >
> > Lastest code:
> >
> > Dim rs As New ADODB.Recordset 'Dim variables required in procedure
> > Dim cn As ADODB.Connection
> > Dim strSql As String
> >
> > Set cn = CurrentProject.Connection 'Create connection and recordsets
> > Set rs = New ADODB.Recordset
> >
> > strSql = "SELECT tblMMD.mmdID,tblMMD.DateOfMeeting FROM tblMMD WHERE
> > tblMMD.DateOfMeeting >= #" & Format$(Me!txtFrom.Value, "dd-mm-yyyy") & "# And
> > tblMMD.DateOfMeeting <= #" & Format$(Me!txtTo.Value, "dd-mm-yyyy") & "# " &
> > "ORDER BY tblMMD.DateOfMeeting"
> >
> > Debug.Print strSql
> >
> > rs.Open strSql, cn, adOpenStatic, adLockOptimistic
> > nMeet = rs.RecordCount
> > MsgBox nMeet
> >
> > ' i added this to see the results
> >
> > Dim tmpdate As Date
> >
> > If Not (rs.BOF And rs.EOF) Then
> > Do While rs.EOF = False
> > tmpdate = rs!DateOfMeeting
> > MsgBox tmpdate
> > rs.MoveNext
> > Loop
> > Else
> > ' There is no data in the recordset
> > sBody = vbCrLf + vbCrLf + "No Meetings" + vbCrLf + vbCrLf
> > End If
> >
> > rs.Close
> >
> > debug result :
> > SELECT tblMMD.mmdID,tblMMD.DateOfMeeting FROM tblMMD WHERE
> > tblMMD.DateOfMeeting >= #01-04-2005# And tblMMD.DateOfMeeting <= #30-04-2005#
> > ORDER BY tblMMD.DateOfMeeting
> >
> > "RoyVidar" wrote:
> >
> > > The FORMAT of a Date/Time field, does
> > > not ensure whether or not time fraction
> > > is stored witihin it I'm afraid, it only
> > > controls the view/display of it. If the
> > > field is populated through for instance
> > > the Now() function, it will contain time
> > > fraction regardless of format.
> > >
> > > Now, we don't see your current code
> > > (which may or may not include my typo
> > > - an extra closing paranthesis ;-) ), so
> > > I'd suggest the following;
> > >
> > > Post the current code, also post the
> > > result of doing a
> > >
> > > Debug.Print strSql
> > >
> > > after assigning the value (found when
> > > using ctrl+g) - btw, the result there, you
> > > should be able to copy/paste into the
> > > SQL view of the query builder, and it
> > > should also work.
> > >
> > > Your dates, hovewer, indicates that
> > > formatting them while concatenating
> > > the SQL string, is needed.
> > >
> > > Paul wrote in message
> > > <27BB7EF4-1504-4557-8758-CD82F5E0CA21@xxxxxxxxxxxxx> :
> > > > thanks all
> > > >
> > > > I have made the changes sugested, thanks for additional advice re naming,
> > > > and checked the table and forms etc.
> > > >
> > > > The date field is setup as a short date and doesn't contain time details.
> > > >
> > > > It does run with Roys suggestion. I have set the dates on the form to
> > > > 1/4/2005 to 30/4/2005, but it is still pulling records out prior to 1/4/2005
> > > > but not the records after 30/4/05.
> > > >
> > > > any other advice ?
> > > >
> > > > cheers
> > > >
> > > > Paul
> > > >
> > > >
> > > > "Douglas J. Steele" wrote:
> > > >
> > > >> One other point, in addition to all the great advice Roy's given you, is
> > > >> that if tblMMD.DateOfMeeting also contains time information, then And
> > > >> (tblMMD.DateOfMeeting) <= #" &To & "#)... isn't going to return any of the
> > > >> meetings that will occur on the actual day input for To. This is because a
> > > >> Date field stores date/time as an 8 byte floating point number where the
> > > >> integer part represents the date as the number of days related to 30 Dec,
> > > >> 1899, and the decimal part represents the time as a fraction of a day.
> > > >>
> > > >> For instance, today (11 Sept, 2005) is 38606. 08:00 this morning would be
> > > >> represented as 38606.33333 (since 08:00 represents one third of the way
> > > >> through today) Therefore, when you're comparing the date/time of the meeting
> > > >> to the date that was input, it wouldn't meet the <= comparison.
> > > >>
> > > >> --
> > > >> Doug Steele, Microsoft Access MVP
> > > >> http://I.Am/DougSteele
> > > >> (no e-mails, please!)
> > > >>
> > > >>
> > > >>
> > > >> "RoyVidar" <roy_vidarNOSPAM@xxxxxxxx> wrote in message
> > > >> news:mn.5b157d59e954a3de.33955@xxxxxxxxxxx
> > > >>> Paul wrote in message <525DA7B3-F697-4539-8248-264A4F3D5878@xxxxxxxxxxxxx>
> > > >>>>
> > > >>>> Brendan
> > > >>>>
> > > >>>> I did have the statement,(statement at the end of post), with that change
> > > >>>> in, but it includes all the records prior to the "from" date. What i am
> > > >>>> trying to acheive is to get the records between the two dates
> > > >>>>
> > > >>>> ta
> > > >>>>
> > > >>>> Paul
> > > >>>>
> > > >>>> "Brendan Reynolds" wrote:
> > > >>>>
> > > >>>>> Where you have ...
> > > >>>>>
> > > >>>>> And <=
> > > >>>>>
> > > >>>>> .... you should have ...
> > > >>>>>
> > > >>>>> And tblMMD.DateOfMeeting <=
> > > >>>>>
> > > >>>>> --
> > > >>>>> Brendan Reynolds (MVP)
> > > >>>>>
> > > >>>>> "Paul" <Paul@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > >>>>> news:4FC8E952-05A1-48B6-B9FE-F852FDDAB336@xxxxxxxxxxxxxxxx
> > > >>>>>> I am running the code below but keep getting syntax error.
> > > >>>>>>
> > > >>>>>> "from" and "to" are date fields on my form holding the dates used for
> > > >>>>>> the
> > > >>>>>> criteria
> > > >>>>>>
> > > >>>>>> help appreciated
> > > >>>>>>
> > > >>>>>> Paul
> > > >>>>>>
> > > >>>>>>
> > > >>>>>> Dim rs As New ADODB.Recordset
> > > >>>>>> Dim cn As ADODB.Connection
> > > >>>>>> Dim str As String
> > > >>>>>>
> > > >>>>>> Set cn = CurrentProject.Connection
> > > >>>>>> Set rs = New ADODB.Recordset
> > > >>>>>>
> > > >>>>>>
> > > >>>>>> str = "SELECT tblMMD.mmdID,tblMMD.DateOfMeeting FROM tblMMD WHERE
> > > >>>>>> (((tblMMD.DateOfMeeting) >= #" + From + "# And <= #" + To + "#))ORDER
> > > >>>>>> BY
> > > >>>>>> tblMMD.DateOfMeeting"
> > > >>>>>>
> > > >>>>>> rs.Open str, cn, adOpenStatic, adLockOptimistic
> > > >>>>>> nMeet = rs.RecordCount
> > > >>>>>>
> > > >>>>>> MsgBox nMeet
> > > >>>>>>
> > > >>>>>> etc
> > > >>>>>>
> > > >>>>>> this does work but does not give the correct record count total:
> > > >>>>>>
> > > >>>>>> str = "SELECT tblMMD.mmdID,tblMMD.DateOfMeeting FROM tblMMD WHERE
> > > >>>>>> (((tblMMD.DateOfMeeting) >= #" + From + "# And (tblMMD.DateOfMeeting)
> > > >>>>>> <= #" +
> > > >>>>>> To + "#))ORDER BY tblMMD.DateOfMeeting"
> > > >>>>>>
> > > >>>>>
> > > >>>>>
> > > >>>>>
> > > >>>
> > > >>>
> > > >>> I think I'd be a bit carefull with regards to
> > > >>> naming convention. Str is a VBA.Conversion
> > > >>> function, To is used within VBA, and,
> > > >>> though probably not relevant here, From is a
> > > >>> SQL-clause. Standard naming conventions will
> > > >>> for instance prefix text controls with txt.
> > > >>>
> > > >>> Usual concatenation operator in VBA is "&"
> > > >>> (ampersand).
> > > >>>
> > > >>> Depending on locale (date settings), you
> > > >>> might need to do some additional formatting,
> > > >>> see for instance Allen Browne's article
> > > >>> http://allenbrowne.com/ser-36.html
> > > >>>
> > > >>> Depending on whether or not .recordcount
> > > >>> is supported by provider, you might get
> > > >>> -1 as result when calling this property
> > > >>> of an ADO recordset. To be on the safe
> > > >>> side, you might try using
> > > >>>
> > > >>> "Select count(*) from ..."
> > > >>>
> > > >>> in stead of opening a recordset and use
> > > >>> the .recordcount property.
> > > >>>
> > > >>> Here's a quickly thrown together
> > > >>> suggestion that might work with the
> > > >>> original approach (some renaming (the
> > > >>> form controls, the variable), using
> > > >>> ampersand, formatting to an
> > > >>> unambiguous date format)...
> > > >>>
> > > >>> strSql = "SELECT tblMMD.mmdID,tblMMD.DateOfMeeting " & _
> > > >>> "FROM tblMMD " & _
> > > >>> WHERE tblMMD.DateOfMeeting >= #" & _
> > > >>> format$(me!txtFrom.value, "yyyy-mm-dd") & _
> > > >>> "# And tblMMD.DateOfMeeting) <= #" & _
> > > >>> format$(Me!txtTo.value, "yyyy-mm-dd") & "# " & _
> > > >>> "ORDER BY tblMMD.DateOfMeeting"
> > > >>>
> > > >>> Since the approach my suggestion is based
> > > >>> on worked, but with wrong count, I'm guessing
> > > >>> that the date format might be the issue.
> > > >>>
> > > >>> Note hovewer that if either the fields
> > > >>> or controls contain time fraction in
> > > >>> addition to dates, you might be up for
> > > >>> more work.
> > > >>>
> > > >>> --
> > > >>> Roy-Vidar
> > > >>>
> > > >>
> > > >>
> > > >>
> > >
> > > --
> > > Roy-Vidar
> > >
> > >
>
.

Relevant Pages

  • RE: hope this is an easy one for somebody
    ... gunk I couldn't get the sub to run. ... It ended up that I had been filtering both the original qry and the strSQL, ... Dim strSQL As String ... 'Note that you could use a condtion in the above sql ...
    (microsoft.public.access.formscoding)
  • RE: hope this is an easy one for somebody
    ... gunk I couldn't get the sub to run. ... It ended up that I had been filtering both the original qry and the strSQL, ... Dim strSQL As String ... 'Note that you could use a condtion in the above sql ...
    (microsoft.public.access.formscoding)
  • Re: Programatically Change Joins
    ... you can change the SQL in a QueryDef object without any problem. ... Dim qdfCurr As DAO.QueryDef ... Dim strSQL As String ... "Sandy" wrote in message ...
    (microsoft.public.access.modulesdaovba)
  • Re: VBScriptADO Max Length of String
    ... I understand the changes to the SQLODEDB driver rather than the "SQL Server" driver and it was something I was working on when your reply arrived. ... Dim objFSO, objFile, strServers, arrServers, strServer ... Dim strTableName, strSql, objConn, strSvrName, strDBName ...
    (microsoft.public.windows.server.scripting)
  • Re: Which identity?
    ... > Some postings I read suggested using MAXto retrieve the inserted record ... > each of these 3 SQL features? ... Another way to do it if using the .AddNew method on a Jet based ADO ... Dim rsTest As ADODB.Recordset ...
    (microsoft.public.access.queries)