Our front ends for our data is in access and has all the queries, forms, and
reports with linked tables to a sql server back end. My question is when i
write select, update, insert into, or even delete queries in the front end do
I use # # for dates or ' ' for dates?

As I recall, this depends on your query.

If you are updating a linked table, I believe the appropriate delimiter is #

However, if you are writing a Pass-thru query, I believe the delimiter is
the apostrophe.

HTH
Dale


"seeker" <(E-Mail Removed)> wrote in message
news:6673DBF6-F804-4EEB-A3AA-(E-Mail Removed)...
> Our front ends for our data is in access and has all the queries, forms,
> and
> reports with linked tables to a sql server back end. My question is when
> i
> write select, update, insert into, or even delete queries in the front end
> do
> I use # # for dates or ' ' for dates?

On Tue, 10 Feb 2009 14:01:01 -0800, seeker <(E-Mail Removed)>
wrote:

>Our front ends for our data is in access and has all the queries, forms, and
>reports with linked tables to a sql server back end. My question is when i
>write select, update, insert into, or even delete queries in the front end do
>I use # # for dates or ' ' for dates?

If you're using a linked table, the query will be in Access syntax - with a #
date delimiter, your choice of ' or " delimiters for text, etc. If you use a
PassThrough query you will use SQL/Server syntax.
--

John W. Vinson [MVP]

Thank you to the two of you. Does the syntax make a difference if the sql
script is embedded in the vba that is run by a button on a form? When the
sql script is embedded within vba should the dates be #??/??/??# or
'??/??/???? 00:00:00'

"John W. Vinson" wrote:

> On Tue, 10 Feb 2009 14:01:01 -0800, seeker <(E-Mail Removed)>
> wrote:
>
> >Our front ends for our data is in access and has all the queries, forms, and
> >reports with linked tables to a sql server back end. My question is when i
> >write select, update, insert into, or even delete queries in the front end do
> >I use # # for dates or ' ' for dates?
>
> If you're using a linked table, the query will be in Access syntax - with a #
> date delimiter, your choice of ' or " delimiters for text, etc. If you use a
> PassThrough query you will use SQL/Server syntax.
> --
>
> John W. Vinson [MVP]
>

On Tue, 10 Feb 2009 21:54:01 -0800, seeker <(E-Mail Removed)>
wrote:

>Thank you to the two of you. Does the syntax make a difference if the sql
>script is embedded in the vba that is run by a button on a form?

No. It's still processed by the JET/ACE database engine and automatically
translated to SQL/Server syntax for you; use the JET conventions.
--

John W. Vinson [MVP]

ok i have an update query as follows
update los_service set los_end_time = #10:15:00 am#, nbr_of_minutes = 105
where member_number = 942 and los_service_date = #2/5/2009# and los_beg_time
= #08:30:00 am# and los_end_time = #10:30:00 am#

this does not update the table I have tried changing time to hh:mm AM/PM
and that does not work either. What is wrong????

"John W. Vinson" wrote:

> On Tue, 10 Feb 2009 21:54:01 -0800, seeker <(E-Mail Removed)>
> wrote:
>
> >Thank you to the two of you. Does the syntax make a difference if the sql
> >script is embedded in the vba that is run by a button on a form?
>
> No. It's still processed by the JET/ACE database engine and automatically
> translated to SQL/Server syntax for you; use the JET conventions.
> --
>
> John W. Vinson [MVP]
>

On Wed, 11 Feb 2009 08:09:02 -0800, seeker <(E-Mail Removed)>
wrote:

>ok i have an update query as follows
> update los_service set los_end_time = #10:15:00 am#, nbr_of_minutes = 105
>where member_number = 942 and los_service_date = #2/5/2009# and los_beg_time
>= #08:30:00 am# and los_end_time = #10:30:00 am#
>
>this does not update the table I have tried changing time to hh:mm AM/PM
>and that does not work either. What is wrong????

Presumably there is no record in the table where those criteria are met
exactly. A Date/TIme field is actually a Double Float count of days and
fractions of a day (times) since midnight, December 30, 1899; the Double has
an accuracy of microseconds, even though you can only display a time to the
second.

What do you see if you just do a select query

SELECT los_beg_time, los_end_time from los_service
WHERE member_number = 942 and los_service_date = #2/5/2009#;

--

John W. Vinson [MVP]