Stupid dates again!

[Marko Stojovic]

Hi there!

Every time I think I've understood how to handle dates in VBA programmes, it gets back at me big time.

This time, I have a select query which picks up a certain date (select top 1 event_date from x where person_ref=y). This query is in a loop, so the date differs each time the query is run, depending on the y-criterion.

I have actually embedded this select query into an INSERT statement, like this:

currentdb.execute "INSERT into Z values (person_ref,#" & currentdb.createquerydef("","SELECT...").openrecordset.fields(0) & "#))

What happens is that some dates are reproduced in the Z table accurately, but others, like for instance August 2nd, are inserted as 8th of February. Next, I tried to enclose the entire section from the second "currentdb" to the "fields(0)" bit with DATEVALUE() - same result. After this, I tried to include DATEVALUE() in the SELECT statement itself ("select top 1 datevalue(event_date)"...) - same result. Then I tried both, again same result!

My question is, what does one have to do to select a date from one table and successfully insert it into another table?!?

(By the way, I'm in the UK, hence the whole confusion. I realise the query could be done more efficiently with parameters etc, but it will only be run once and that doesn't really matter!)

Thanks in advance!

Marko

[This message has been edited by Marko Stojovic (edited 04-10-2002).]

 

[simongallop]

Check that you locale settings are English (UK) NOT English (US). These can be found in the control panel of windows.

 

[Marko Stojovic]

Yes, that aspect's in order. Everything is set to English. I don't actually have a wider problem with dates. In tables, dates always appear normally. For instance, 31/08/01 will appear as just that, not as 08/31/01.

 

[Rich]

Why don't you just use DoCmd.RunSQL "INSERT INTO etc using the Where clause, it's much easier than having to Format the date in a string which I suspect is the problem you have.

 

[Marko Stojovic]

I would do that, but the thing is that my only means of retrieving the date (which changes in each loop) is a SELECT statement. The output of this SELECT statement (the date) therefore serves as one of the criteria in the INSERT statement, and would have to remain such regardless of whether the query was done via DoCmd.RunSQL or CurrentDb.Execute.

For this reason, commas are unavoidable unless I use parameters. However, I don't think a parameter can be used in the VALUES() section of an INSERT query, only in the WHERE section to represent a variable criterion.

[This message has been edited by Marko Stojovic (edited 04-10-2002).]

 

[Rich]

Do you mean something like this?
DoCmd.RunSQL "INSERT INTO Deductions ( TrDate, Debits, Heading, HowPaid, [Desc], Payee )SELECT DateDue.DteDue, DateDue.PaymentAmount, DateDue.Heading, DateDue.PymtType, DateDue.Description AS Det, DateDue.Payee FROM DateDue WHERE (((DateDue.DteDue)<=Date()) AND ((DateDue.PaymentAmount)<>0) AND ((DateDue.Appended)=No));"

 

[Marko Stojovic]

I've just tried to use that method. However, the SELECT part of the query needs to use the keyword TOP and an associated ORDER BY clause, all in the context of the INSERT INTO statement. Access doesn't appear to like this, and gives an error message saying that 'event_date' is not a field (which it blatantly is!)

DoCmd.RunSQL "insert into TABLE_Y " & _
"select top 1 person_ref, event_date from TABLE_X where person_ref=" & rstPersonRefs.Fields(0).Value & _
" order by event_date asc"

(rstPersonRefs has its cursor on the current person ref, and the cursor is moved before each loop)

The problem is that there is not only one eligible person-date combination in the X table - a person may have several associated dates. I have to choose the EARLIEST date! That's why I feel the need to use TOP 1, given that merely using the criterion "where person_ref=n" will return all the person-date combinations.

If I use two steps, we're back with the scenario I was using originally, where the date to be inserted by the INSERT query is accessed by looking at a recordset. This is where the american format creeps in.

Marko

[This message has been edited by Marko Stojovic (edited 04-10-2002).]

 

[Marko Stojovic]

Please guys, this is a matter of principle!

 

[NickBridgens]

As a junior, please forgive my presumption, however, I read elsewhere that all dates in SQL are US, irrespective of who or what you think you are.

I've just gone down the route of a delete statement that deleted all subrecords everything later than today, but left a couple of items in July and August and it wasn't till then I realised that I was deleting all after 4th November 02 and not 11th April 02. You'll need to reformat your dates into US (format(date,"mm/dd/yy")) before you use them.

Nick Bridgens

 

[Rich]

You only have to Format the date to U/S when using strSQL, dates in UK format work fine in SQL.

 

[NickBridgens]

I guess I have to learn that a man has to know his own limitations!

 

[Marko Stojovic]

Thanks for that!
I always tend to use Datevalue and enclose the date as a string, as it seems to work (up till now).

I also thought that any SQL statement executed in Access is processed by assuming the dates are American. Can you say a bit more about this difference between strSQL and SQL in that respect, Rich? Do you mean, it's just when you use embedded SQL in VBA that it goes all American?

By the way, I will try the Format option, I think maybe that will work. Thanks for all your advice. You'll hear back from me if it fails again!

Marko

 

[Rich]

Here's one post on the subject http://www.access-programmers.co.uk/ubb/Forum3/HTML/002631.html
and another http://www.access-programmers.co.uk/ubb/Forum1/HTML/007581.html

[This message has been edited by Rich (edited 04-12-2002).]

 

[Marko Stojovic]

Wicked!

Actually, the Format thing has worked wonders for me.

It sorted *most* of the dates out correctly when I applied it to the outer statement which reads the output of a date-returning querydef.

It sorted out *all* the dates, though, when I applied it within the date-returning querydef itself

[i.e. ...CreateQueryDef("","SELECT <field1>,Format(DATE,'MM/DD/YY') FROM...").OpenRecordset.Fields(0)]

this phrase above serving as a criterion in an enveloping INSERT statement - in the first case I had enclosed the whole of the querydef with 'Format()'.

It appears to me that you have to do the format thing as soon as you acquire the date for the first time. As soon as a British-looking date makes its way into the query (as a result of not using a Format statement at the first contact with the date, i.e. in the very select statement where the date was first picked up from a table), the query starts doing wonderful things.

I'm sorry for the ramble, I just thought I'd share what I think might be a possible conclusion, as it seems to matter *where* you use your Format command in the query rather than just *whether* you use it or not.

Thank you all for your help (and the links) - I should give this site a percentage of my pay!

[This message has been edited by Marko Stojovic (edited 04-12-2002).]

 

[Rich]

I'm collecting on their behalf

[This message has been edited by Rich (edited 04-12-2002).]