Re: Syntax error

Tech-Archive recommends: Fix windows errors by optimizing your registry

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: Formatting and browser compatibility
    ... >> and have some good formatting, as long as the user is using IE. ... >> browsers in question are Firefox and Safari. ... The major problem is with multline text controls ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Syntax error
    ... Dim str As String ... for instance prefix text controls with txt. ... might need to do some additional formatting, ... Since the approach my suggestion is based ...
    (microsoft.public.access.modulesdaovba)
  • Re: Which event would be best
    ... "Wayne Morgan" wrote: ... > values when one of the controls changes. ... > The syntax for the formatting: ... I would like the assessed field to be bolded and red. ...
    (microsoft.public.access.formscoding)
  • RE: Formatting and browser compatibility
    ... have you looked into using the Browsercap tag in the config file. ... Should help with 90% of the formatting. ... Firefox and Safari both render the location of controls, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: trouble with report that has lots of controls
    ... If you have lots of controls that you want to run the same code, ... >> MS Access MVP ... >>>I have narrowed it down to the conditional formatting. ...
    (microsoft.public.access.reports)