Syntax error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"
 
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
 
Paul wrote in message
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
Click to expand...


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.
 
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.
 
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 said:
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.
Click to expand...
 
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
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
Click to expand...
 
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
 
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.
 
The format string is

"yyyy-mm-dd"

not

"dd-mm-yyyy"

see - the jet engine doesn't know what
to "think" by the last format, and
interprets the first numbers as month,
the next as days (US style), unless
the number in the first part exceeds
12 ... so - an unambiguous format is
needed, either what I provided, which
I'm lead to believe is a Ansi standard,
or something like the article I
referenced.

Paul wrote in message
 
Roy

cheers, i think I must have misunderstood what you put about the yy/mm/dd
and changed it back to reflect the date format of the field holding the data
(!opps)

Thanks for all your help
 
Back
Top