pass thru query

bobh

unread,

Nov 5, 2012, 3:41:48 PM11/5/12

to

Hi All,

I have this Access2003 query that I want to make a 'passthru' query
but it has a value it needs to get from a form. Do I remember
correctly that a pass thru query needs to know the value and can not
get it from an Access object?? if so then how do I ask the user to
enter a date that the query would be able to have?

the access query is this
UPDATE tblStBMthlyWorking SET tblStBMthlyWorking.ExtractDte = [Forms]!
[frmMainMenu]![bxExtractDte]

thanks
bobh.

Bob Barrows

unread,

Nov 5, 2012, 6:22:13 PM11/5/12

to

You will need to use VBA to dynamically build the sql statement and set it
to the query's SQL property.

bobh

unread,

Nov 8, 2012, 4:10:27 PM11/8/12

to

thanks for the idea....... as a test I setup a querydef with the
below line and then did a docmd to run the passthru query and when it
finished the ExtractDte field in the table on SQLServer as a value of
1/1/1900.... so I did something wrong but don't know what, any ideas?
the ExtractDte field in the SQLServer table is a 'smalldatetime'
datatype

UPDATE MarsUsers.tblMarsImport SET ExtractDte = 01/ 01/2000

thanks
bobh

Bob Barrows

unread,

Nov 8, 2012, 5:06:05 PM11/8/12

to

You used the wrong date format and the wrong datatype. The statement sent to
SQL Server should read:
UPDATE MarsUsers.tblMarsImport SET ExtractDte = '20010101'

so you need to write your concatenation code so that will be the resulting
string.

Yes, '20010101' is a string, but in SQL Server, a string containing a date
in a recognizable format will be implicitly converted to datetime.

Phil

unread,

Nov 8, 2012, 7:10:23 AM11/8/12

to

Dare say Bob is right, I know nothing of SQLServer syntax.
However I am reasonably certain that 01/01/2000 will be regarded as a long
division with an answer of 0.0005 and format(cdate(0.0005),"long date") comes
out as 30 December 1899 If Bob's solution doesn't worki, try SET ExtractDte =
#01/ 01/2000#

Phil

Bob Barrows

unread,

Nov 8, 2012, 5:38:01 PM11/8/12

to

Correct - that is why I said he used the wrong datatype.

> and format(cdate(0.0005),"long
> date") comes out as 30 December 1899

Correct in Jet, but not SQL Server, as the OP's results bear out.

> If Bob's solution doesn't worki,
> try SET ExtractDte = #01/ 01/2000#
>

That will work in Jet, but not in SQL Server, which will not recognize the #
delimiters.

bobh

unread,

Nov 9, 2012, 9:42:36 AM11/9/12

to

> in a recognizable format will be implicitly converted to datetime.- Hide quoted text -
>
> - Show quoted text -

I was curious about your statement

> a string containing a date in a recognizable format will be implicitly converted to datetime<

and since the users want to see dates as mm/dd/yyyy
I built my querydef like this -> UPDATE MarsUsers.tblMarsImport SET
ExtractDte='" & Me.bxExtractDte & "'"
and the users enters the date like this -> 11/10/2012
the passthru runs and the value in the ExtractDte field in the
SQLServer table equals - 11/10/2012

Perfect, thanks for your help :)
bobh

Bob Barrows

unread,

Nov 9, 2012, 10:58:25 AM11/9/12

to

How the users see dates and how dates are stored and recognized by the
database engine are two very different things.
In SQL Server, datetimes are stored as paired integers, the first
representing the # days since the seed date and the second representing the
# of msec's since midnight.
This is different from Jet, which stores them as decimal numbers, with the
whole number representing the days since the seed date and the decimal
representing the time of day (.0 = midnight, .5 = noon).
As you can see, no format is stored. Format is applied when dates are
displayed to users. This is true for both Jet and SQL Server.

> I built my querydef like this -> UPDATE MarsUsers.tblMarsImport SET
> ExtractDte='" & Me.bxExtractDte & "'"
> and the users enters the date like this -> 11/10/2012
> the passthru runs and the value in the ExtractDte field in the
> SQLServer table equals - 11/10/2012
>
> Perfect, thanks for your help :)

So you lucked out and ran this on a server that had US date format as its
default. This will not always be the case.
Your goal as a programmer should be to deliver date literals in a format
that will always be unambiguous and recognizable to the database engine.

Jet will correctly interpret literals supplied in US format (mm/dd/yyyy) and
ISO format (yyyy-mm-dd). The latter is preferable since it is completely
unambiguous, regardless of the regional settings on the machine where Access
is installed.

SQL Server will usually correctly interpret dates supplied in US format (but
not always) and will always correctly interpret dates supplied in ISO
format: yyyymmdd. If you wish to bullet-proof your code, you should change
it to:
... "UPDATE MarsUsers.tblMarsImport SET
ExtractDte='" & year(Me.bxExtractDte) & month(Me.bxExtractDte) &
day(Me.bxExtractDte) & "'"

Bob Barrows

unread,

Nov 9, 2012, 11:54:01 AM11/9/12

to

Bob Barrows wrote:
> SQL Server will usually correctly interpret dates supplied in US
> format (but not always) and will always correctly interpret dates
> supplied in ISO format: yyyymmdd. If you wish to bullet-proof your
> code, you should change it to:
> ... "UPDATE MarsUsers.tblMarsImport SET
> ExtractDte='" & year(Me.bxExtractDte) & month(Me.bxExtractDte) &
> day(Me.bxExtractDte) & "'"

Oops, that was a goof. I don't want to lead you astray so:

... "UPDATE MarsUsers.tblMarsImport SET
ExtractDte='" & year(Me.bxExtractDte) & right("0" &
month(Me.bxExtractDte),2) &
right("0" & day(Me.bxExtractDte)),2) & "'"

Phil

unread,

Jan 8, 2013, 4:23:08 AM1/8/13

to

On 08/11/2012 22:06:05, "Bob Barrows" wrote:

Dare say Bob is right, I know nothing of SQLServer syntax.
However I am reasonably certain that 01/01/2000 will be regarded as a long

division with an answer of 0.0005 and format(cdate(0.0005),"long date") comes
out as 30 December 1899 If Bob's solution doesn't worki, try SET ExtractDte =
#01/ 01/2000#

Phil

Bob Barrows

unread,

Jan 8, 2013, 6:45:44 AM1/8/13

to

Phil wrote:
>
> Dare say Bob is right, I know nothing of SQLServer syntax.
> However I am reasonably certain that 01/01/2000 will be regarded as a
> long division

I wonder why this got sent a second time, two months later ... ?

Phil

unread,

Jan 8, 2013, 7:30:38 AM1/8/13

to

My Newsreader is a tempremental beast. Sometimes doesn't send messages, and
then I fid a whole lot in the Queue and it sends them all at once.

My apologies

Phil

PS Do you have a better free newsreader for Windows 7. I use Newsman.

Gene Wirchenko

unread,

Jan 8, 2013, 11:58:52 AM1/8/13

to

On Tue, 8 Jan 2013 12:30:38 GMT, "Phil" <ph...@stantonfamily.co.uk>
wrote:

[snip]

>PS Do you have a better free newsreader for Windows 7. I use Newsman.

I like Forte's Agent. I use it under XP though so I can not
specifically comment on using it with 7. There is a new version out
(which I have not gotten to yet). Forte's homepage is
http://www.forteinc.com/main/homepage.php
The USENET newsgroup for it is:
alt.usenet.offline-reader.forte-agent

Sincerely,

Gene Wirchenko

Phil

unread,

Jan 8, 2013, 1:31:54 PM1/8/13

to

Tried that & liked it, but we poor old pensioners have to look for a freebe

Phil

Joan Wild

unread,

Jan 8, 2013, 4:17:28 PM1/8/13

to

Phil wrote:
>
> Tried that & liked it, but we poor old pensioners have to look for a
> freebe
>
> Phil

I use XanaNews and quite like it. It's good for offline reading if you
have a limited internet connection; does everything I need.

http://xananews.techtips.com.br/

Joan

The Frog

unread,

Jan 9, 2013, 12:45:45 AM1/9/13

to

Thunderbird. Outstanding as an app for both news and mail. Free. Made
by the same people who make FireFox, and if I'm right previously made
Netscape.

--
Cheers

The Frog