odd query issue

[finglem]

Hi Folks

Bit of a weird one this. I've got two access databases same version of access, exactly the same structure (just store info for two websites)

I go into one and do a simple query and I get back the expected results. Go into the other copy, and paste the exact same sql statement and I either get a "data type mismatch" error or a "driver may be out of resources" error

Been driving me mad for days.

Anyone got any ideas?

As i say it's just a simple query:

SELECT Order.* FROM [Order] WHERE (((DateValue([Date Ordered]))>#01/01/09#))

Thanks

 

[traingamer]

You may have an invalid date in [Date Ordered] in the second database and the select is choking on it.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[traingamer]

It's sometimes easier if you only post questions once, so we don't have to repeat the answers in: thread701-1572025

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[finglem]

Thanks for responding

Sorry for posting twice..Couldn't find the first thread..thought i must have done something stupid!! Obviously did!!

I wouldn't have thought an invalid date was possible as it gets populated by the program at the front end if you see what i mean, if one was wrong, I would expect all of them to be wrong.

I suppose the data could be corrupted I'll look into it in the morning and let you know.

Thanks

 

[finglem]

Hi Thanks

There was indeed a null value in one of the date fields. checking for not(isnull([date ordered]))
seems to return the expected data.

Thanks again

 

[dhookom]

finglem,
You might want to thank traingamer by giving him a star. That way, he gets awarded and others know the thread has been successfully concluded.

Duane
Hook'D on Access
MS Access MVP

 

[traingamer]

Majp actually answered it similarly (and earlier) in the other thread, but I didn't see it until after I posted.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard