Re: DateDiff bug?

Odder & Odder

The query runs OK, but if I make the slightest change, such as moving the
position of a table in the design view, I get the same error. Have even
created a new database and imported everything in to it.

Phil

"Phil Stanton" <phil@xxxxxxxxxxxxxxxxxx> wrote in message
news:q9OdnTLDsqHFIr_XnZ2dnUVZ8qydnZ2d@xxxxxxxxxxxxxxxxxxx
Thanks for coming back, Bob

DatePaid is an unbound field on the form, Default value = Now() and format
is General Date.
I am always a bit wary about dates because of the difference in English
dates (Day, Month, Year) and American dates (Month, Day, Year).

If I change the format statement to "General Date" instead of
conDateFormat, I still get the same problem.

Phil

"Bob Quintal" <rquintal@xxxxxxxxxxxxx> wrote in message
news:Xns9C1C5628ECA35BQuintal@xxxxxxxxxxxxxxxx
"Phil Stanton" <phil@xxxxxxxxxxxxxxxxxx> wrote in
news:u9CdnSag7JEez7_XnZ2dnUVZ8sOdnZ2d@xxxxxxxxxxxxxxxx:

I am creating a query from VBA using AK2

I use

Const conDateFormat = "\#dd\/mm\/yyyy\ Hh:Nn:Ss#"

SQLStg = "SELECT Payments.* "
SQLStg = SQLStg & "FROM Member INNER JOIN Payments ON
Member.MemberID =
Payments.MemberID "
SQLStg = SQLStg & "WHERE (Member.MemHeadOfHouseID = " &
MemHeadOfHouseID
SQLStg = SQLStg & ") And (DateDiff('s', PaymentDate, " &
Format(DatePaid, conDateFormat) & ") < 5)" 'Last transactions
SQLStg = SQLStg & " ORDER BY Payments.PaymentID;"

The SQLStg is

SELECT Payments.* FROM Member INNER JOIN Payments ON
Member.MemberID = Payments.MemberID WHERE (Member.MemHeadOfHouseID
= 3) And (DateDiff('s', PaymentDate, #31/05/2009 10:01:06#) < 5)
ORDER BY Payments.PaymentID;

When I past the SQLStg into a query SQL , I can see it in Design
view and run it. There appears to be no problem, but it returns no
records. If in design view I change anything say the
MemHeadOfHouseID from 3 to 13 I get a
Syntax error (missing operator) in query expression
'(((Member.MemHeadOfHouseID) = 13) AND ((01:06#))<5))'
Somehow it is just picking up the end of the date.

If I cut the time element out then the query runs OK
If I paste the time element back, again the query runs OK

Any ideas please

Thanks

Phil

Why are you using one format statement in the datediff() function?
Is one of the date fields a string and not a datetime type?
Does it work if neither date is formatted? Both?
What happens when you replace the constant with a litteral?

Sounds more like a format bug than a datediff bug
--
Bob Quintal

PA is y I've altered my email address.




.

Relevant Pages

  • Telephone Number Format
    ... phone numbers without format. ... Query works fine if they are asked for in the displayed and ... would rather just key in the raw 10 digit sequence. ... I have changed the field definitions in the table design, ...
    (microsoft.public.access.queries)
  • RE: case sensitive join
    ... You could try going into Design View and either checking the format of the ... display) your records of that column in a uniform format. ... If you still get duplicate results, check your query results to see ...
    (microsoft.public.access.queries)
  • RE: Access 2007 mail merge
    ... Use a query which formats the date as the source for the merge. ... design view enter something like this in the 'field' row of a blank column: ... The Format function returns a string expression, ... from my database into my word document the date shows in numerical form. ...
    (microsoft.public.access.gettingstarted)
  • Re: DateDiff bug?
    ... DatePaid is an unbound field on the form, Default value = Nowand format ... If I change the format statement to "General Date" instead of conDateFormat, ... SQLStg = SQLStg & "FROM Member INNER JOIN Payments ON ... When I past the SQLStg into a query SQL, I can see it in Design ...
    (comp.databases.ms-access)
  • RE: Saving Query as Word Document changes the look of data
    ... I designed a query in Access, to give me just the Cities, States & Zip Codes ... I then wanted to have it in a word processing format so that I can ... The text of each field, however, appeared with quotations around it, e.g., ... Can I change the design somehow so that when it's ...
    (microsoft.public.access.queries)