Date Problem RunSQL Can't Figure Out!!!

[mbarnett]

Hi All,

Got an issue with SQL statement. My problem is I import a text file 4 times a day (data is always updated). The first import may contain 20 lines the next 50 always growing.

To avoid double counting (since the import always has the previous data) I tried to use the below SQL statementto delet any data from my table with the current date. Currently I have to go into the statement and change the date manually.

I tried using the Date() varaible but since my import date is text (18Feb05) it does not work...I tried changing the import from text to date, but I get conversion errors and the date disappears. Any suggestions would be greatly appreciated.


DELETE tblAA_AA_New_DMO.*, tblAA_AA_New_DMO.Date FROM tblAA_AA_New_DMO WHERE tblAA_AA_New_DMO.Date="18Feb2005

 

[RoyVidar]

Looks like QBE syntax, if so, perhaps posting in the query forum forum701, as this is the Access VBA forum.

Anyway, heres something to try, perhaps in QBE

[tt]...WHERE tblAA_AA_New_DMO.[Date]=format(date(),""ddmmmyy"")[/tt]

or in a VBA SQL string

[tt]...WHERE tblAA_AA_New_DMO.[Date]=#" & format(date,"ddmmmyy") & "#"[/tt]

Roy-Vidar

 

[RoyVidar]

...need new glasses, since it's string, not date, the last one should probably read

[tt]...WHERE tblAA_AA_New_DMO.[Date]='" & format(date,"ddmmmyy") & "'"[/tt]

Roy-Vidar

 

[mbarnett]

Thanks for your reply -

I put in what you provided but i got a Syntax error sting in query expression error. Below is what I put in the macro

DELETE tblAA_AA_New_DMO.*, tblAA_AA_New_DMO.Date FROM tblAA_AA_New_DMO WHERE tblAA_AA_New_DMO.[Date]=' " & Format(date,"ddmmyy") & " ' "

Thanks again for the help

 

[RoyVidar]

Where and how are you using this?

See faq181-2886 #14, we don't know whether you're using the query grid or code (btw - three "m"'s, and I forgot two yy's - "ddmmmyyyy")

Roy-Vidar

 

[mbarnett]

I get Syntax error sting in query expression error -

DELETE tblAA_AA_New_DMO.*, tblAA_AA_New_DMO.Date FROM tblAA_AA_New_DMO WHERE tblAA_AA_New_DMO.[Date]=' " & Format(date(),"ddmmyy") & " ' "

Thanks again for all your help!


Michael

 

[mbarnett]

Sorry....The dtae format will always be "ddmmmyy". I'm using the sql statement in a Macro. Still getting Syntax error message.

DELETE tblAA_AA_New_DMO.*, tblAA_AA_New_DMO.Date FROM tblAA_AA_New_DMO WHERE tblAA_AA_New_DMO.[Date]=' " & Format(date(),"ddmmmyy") & " ' "

Thanks Again for your help!

Michael

 

[RoyVidar]

I don't use macros, and didn't know you could use them for query strings either. Don't think this'll work, anyway, it doesn't quite like the format function...

Use either a stored query that you roun through the openquery method of the docmd object, or something like this:

[tt]dim strSql as string
strsql = "DELETE * FROM tblAA_AA_New_DMO " & _
"WHERE tblAA_AA_New_DMO.[Date]='" & Format(date,"ddmmmyy") & "'"
' docmd.setwarnings false ' uncomment when it's working
docmd.runsql strsql
docmd.setwarnings true[/tt]

In the on click event of a button, use the button with three dots, select code builder and paste the above where the cursor is. Unless I've added my usual typos, it might work;-)

Roy-Vidar

 

[Amiel]

change the data type from text to date. This is the real solution to this delima.

If you have no control over the source tables use an intermediate table, parce the source text string using code, thus converting it to the proper data type.

You can write a query that does all of the conversion but I wouldn't. I'd do it using code.

Take a look at the 'Mid()' function. As in

Mid([datestring,1,2) gets the day part
Mid([datestring,3,3) gets the month part
Mid([datestring,6,2) gets the two character year part


The day and year part are pretty straight forward to deal with. the month part would require a lot of nested if statements in your SQL statement (twelve of them to be exact).

Personally id use some code like


dim iMonth as int

if Mid([datestring,3,3) = "jan" then
iMonth = 1
elseif Mid([datestring,3,3) = "feb" then
iMonth = 2
elseif Mid([datestring,3,3) = "mar" then
iMonth = 3
elseif Mid([datestring,3,3) = "apr" then
iMonth = 4
...
...



finally the date string would be build with something like:


dim sDate sa string

sDate = Cstr(iMonth) ' the month
sDate = sDate & "/"

sDate = sDate & Mid([datestring,1,2) 'the day
sDate = sDate & "/"

sDate = sDate & Mid([datestring,6,2) 'the year
sDate = sDate & "/"


then


Dim fDate as Date
fDate = format(CDate(sDate),"mm/dd/yyyy")

I didn't check the last line. But this should be ok,


use plenty of debug.write statements and your debugger [alt-G] output while writing this.

Store your results in an intermediate table. The entire process can be automated - in code, with Access.


note however, this is actually a bad idea from a business process standpoint as the validation should take place at input rather than being left for later conversion and subsequent verification. What happens when a value 'can't' be converted to a date.


Amiel
amielzz@netscape.net