Access 2007 Runtime and dates - SQL

[Rose B]

I have a fairly large/complex application (split - front end) originally
written in 2002 but now converted to 2007. All works fine if running with
2007 full, but idea was (is) to install 2007 Runtime for those users still on
XP with old version of Access.

Problems that I have found so far are all to do with use of Date() in
queries or field default values. On the whole I have managed to get around
these by (labouriously) relacing these with Now() or DateValue(Now()).
Problem that I am left with is that my SQL statements are not working where I
have used a FORMAT of (e.g.) #yyyy\/mm\/dd\#. For some reason the SQL that is
generated looks fine and works in full Access 2007 but Runtime 2007 complains
of a Syntax error (3134). Any ideas on how to overcome this?

 

[gary b]

I have a fairly large/complex application (split - front end) originally
written in 2002 but now converted to 2007. All works fine if running with
2007 full, but idea was (is) to install 2007 Runtime for those users still on
XP with old version of Access.

Problems that I have found so far are all to do with use of Date() in
queries or field default values. On the whole I have managed to get around
these by (labouriously) relacing these with Now() or DateValue(Now()).
Problem that I am left with is that my SQL statements are not working where I
have used a FORMAT of (e.g.) #yyyy\/mm\/dd\#. For some reason the SQL that is
generated looks fine and works in full Access 2007 but Runtime 2007 complains
of a Syntax error (3134). Any ideas on how to overcome this?

Rose...

I seem to recall that SQL does not recognize =Date() (as Access does)
but *does* recognize =Date. Check out the DATE function in SQL.. I
may be all wet!! Perhaps this will help.

gb

 

[Rose B]

More info (and more curious!). The SQL that was resulting in an error was
inserting a record into a table using fields from a form (sourced from
another table), dates as formatted below. What I have found is that if the
source record has been written to the table from the R2007 application, then
the insert SQL works fine, but if the source record was originally written to
the table from an older version, then it isn't. I have tried converting the
back end to 2007 (currently mdb) but this does not make any difference. The
date fields concerned are defined as Short Date in the underlying table.

Another very strange problem that I have is a form where the data source is
a query that uses tables and another query as a source. When a button on the
form is clicked I have an On Click event that sets the values of some of the
fields on the form. If one of these fields is sourced from a field which is
from the query rather than directly from a table then an error is
encountered. Again, all works fine in full 2007. Still investigating and will
post back with any updates but if anyone has come across this kind of thing
before would be very interested.

 

[Rose B]

Thanks Gary - it really gets more and more strange! The SQL seems to work OK
that I had (for the SQL it was just the format that seemed noto to work -
Date() referred to queries and field defaults). HOWEVER - I now seem to have
it working, but all I did was to use a back end that had been opened in full
Access 2007.

I have now found that the other issues that I have (in another post) seem to
'go away' of I click on the relevant button in a form twice when working in
the runtime version. How frustrating!!! (But I think worth the effort to move
to a later/consistent release).

Thanks again for taking the time and thought to respond.

 

[gary b]

Hard to argue with success... Congrats!! Now on to the next
'challenge'!!

gary b