Problem extracting Data from Access 2000 using VB6

[Fibee]

Hi

I currently have a VB6 program that we use to transfer times and dates of completed helpdesk jobs into a time recording system. We recently had an upgrade to our helpdesk system to Access 2000 and I am having great problems getting the vb6 program to work with it. I have added the MS DAO 3.6 object library and I am still getting errors. It is now complaining of a 'malformed guid in query experession' and I have no idea what this means as the sql statement is identical to the one I used when we had Access 97. This is the current sql statement:

stmt = "select WO_NUM,TYPE,ASSNDATE,COMPLETED,RESPONS,HOURS from TASKS where assndate >= {^" & Format(DTPStartDate, "yyyy-mm-dd") & "} and completed <= {^" & Format(DTPEndDate, "yyyy-mm-dd") & "} and completed > {^1995-01-01} and RESPONS = '" & Responsible & "' order by TYPE"

I have a feeling it is something to do with the dates and have tried taking the date formatting out and I actually get data but I get everything and not between the specified dates.

Any help or advice would be appreciated.

Thanks

Fi

 

[zemp]

Have you tried surrounding your date values with the '#' symbol?

...where assndate >= #'" & DTPStartDate & "#' and...

zemp

 

[Fibee]

Hi Zemp

I have tried surrounding the dates with # as you describe and I get no errors but it retrieves all records and not those between the dates.

Fi

 

[johnwm]

Looks like it's seeing the curly brace { as a GUID delimiter. As zemp says, replace your {^ combination with # and your } with #

Then put a [tt]
debug.Print stmt[/tt]
immediately before you use the stmt variable so you can post the result here.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[zemp]

It might be a problem with your where clause. As johnwm said post your completed SQL for us.

zemp

 

[Fibee]

Hi

Right I did as you suggested above and this is the result:

select WO_NUM,TYPE,ASSNDATE,COMPLETED,RESPONS,HOURS from TASKS where HOURS <> NULL and assndate >= #05/07/2004# and COMPLETED <= #11/07/2004# and RESPONS = 'username' order by TYPE

I actually get 13 records and for that criteria I should only get 1. The dates I get range from 14/05/04 through to 13/07/04. Any Ideas?

Fi

 

[CasperTFG]

Try putting the return date format in your select... By the looks of it on your last line I think that the return format is 13/07/04 - DD/MM/YY, however you are selecting with either 11/07/2004 - DD/MM/YYYY or MM/DD/YYYY.

The format that comes out of the SQL is the format that should go in.

Try running that SQL Statement in MSAccess's Query analizer.

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[Fibee]

Hi Casper

Thanks very much for your advice, I entered exactly the same criteria into the design view of access but when I then switched to SQL view I found that Access had switched the months and days around so it was actually selecting betweeen 07/05/04 and 07/11/04 instead of 05/07/04 and 11/07/04 so I formatted my dates in vb to match and it now works perfectly!

Thanks once again

Fi

 

[CasperTFG]

No Problemo...

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".