Error 13 Type Mismatch on OpenReport on specific ID and Date

[Murielle G]

Hello,

I have a quite simple database where i store project data.
I made 4 tables which store various data on the projects, all linked with the ProjectID and, except for the table which gather all the generic info which do not change, also with the ReportDate.

so:
tbl ProjectList: key = Project ID
tbl ProjectStatus, Project.., Project..: keys=ProjectID & ReportDate.

I made a Report out of data stored in those various tables.
On a form, i used comboboxes to filter a first subform which lists the projects by name. When one selects a project, a second subform shows the record linked to this project per ReportDate. I want to open the report on this specific ProjectID and ReportDate using a button.

I manage to open the report on the ProjectID, but it opens ít for all the dates shown in the subform 2. As fast as i add the Date condition to the Where Condition, it says that i have a type mismatch error. I checked everything but don't see what's wrong. Does it have to do with my table structure? (ReportDate missing in the first table?)

Here is my code in VBA:

Private Sub LoadPPM_Click()

DoCmd.OpenReport "PPM-Report", acViewPreview, , "[ProjectID] = " & Me!ID And "[ReportDate] = #" & Me!Subform2.Form!Date & "#"

End Sub

It should not be a problem so it's even more annoying. Thanks to anyone who can help me!

[Mariostg]

How about this?

DoCmd.OpenReport "PPM-Report", acViewPreview, , "[ProjectID] = " & Me!ID & " And "[ReportDate] = #" & Me!Subform2.Form!Date & "#"

[Rabbit]

@Mario, you have an extraneous double quote after the And.

[Mariostg]

Oups, my bad. Was a test for Murielle ;-)

[Mihail]

Take a look to this discussion:
Discussion: SQL Date Literals and Regional Settings

I think that you'll be able to handle your problem.

My advice is to read all the thread and follow all links from that thread.

[Murielle G]

so the answer would be to simply put the "And" after the quotation mark??? I hope it works, i'll try on monday... It seems weird though, the And should not be considered as string but really as a connector in the Where condition, shouldn't it?
Thank you anyway for the answers!

Thanks Mihail, i'll have a look! Sometimes it's just about finding the discussion about a very similar problem...

[NeoPa]

Essentially yes, but that's not quite enough. The date value should also be formatted to the SQL standard (See Literal DateTimes and Their Delimiters (#)). Also, you should look at the SQL string itself when debugging such issues (See How to Debug SQL String) and, of course, compile your code (See Debugging in VBA). These help you avoid such basic issues as improperly formatted SQL strings as they become obvious when you look at the string what you've done wrong.

[NeoPa]

Very irritatingly I find that the same question was posted twice by the same member - which is strictly forbidden. This led to two good sets of answers that now have to be merged together into one, and much wastage of the experts' time. Please ensure this never happens again.

[Murielle G]

Hello,

Thanks again for the anwers!
Sorry NeoPa, i thought my first post hadn't been posted as i registered in the same time and found no link to my post, there was nothing in my profile or message box either... now i know we get emails when someone answers the post!

Top this debugger, i didn't know about it, so thank you.
It's still not working though... i don't understand.
From

StrgSQL = "[ProjectID] = " & Me!ID & " And [ReportDate] = #" & Me!t_Reportsub.Form!Date & "#"
DoCmd.OpenReport "PPM-Report", acViewPreview, , StrgSQL

i get:
StrgSQL = "[ProjectID] = 74 And [ReportDate] = #04.01.2012#"

which is right, isn't it?

There still is the Run-time error 3075, Syntax error in Date in query expression '([ProjectID] = 74 And [ReportDate] = #04.01.2012#'

I don't see any problem...I checked the data type in my tables it's all fine, i'm using date pickers so they're all in the same format...

[Mihail]

Ups !!!
You do not read the entire thread I get you the link.
Try again ! :)

Pay attention to post #2.
Smiley treat exactly your (my) case where date is like #04.01.2012# but VBA can't understand that.

So... try again !!!

[Murielle G]

ooooyéééé SOLVED!!!

Thanks Mihail! I've been a bit too fast on that one.
It works perfectly fine now, but what a pain in the ass, this date problem... I'm glad i posted, i usually go find topics matching my problems and find what i need, i wouldn't have found this one by myself.

I'm newly also an intensiv user of the Immediate Window, thanks for the tip NeoPa!

[NeoPa]

@Murielle.
I appreciate that coming to the forums first time can be confusing. I'm sure you won't be making that mistake again :-)

As for the question though, I think that discussion thread was, indeed, a good one for your situation, but you needn't have gone as far as post #2 to fix the problem as the main issue was to use the Format() function from post #1 (which was the main point you missed in your code). You also suffered from the Regional Settings issue though, so needed to proceed to discover how to overcome that (Although the article linked in the first post covered that too of course). For interest, which country (Set of Regional Settings) are you from?

Murielle G:
I'm newly also an intensive user of the Immediate Window. Thanks for the tip NeoPa!

I'm very pleased you looked at that. You will find that extraordinarily helpful. I do.

[Murielle G]

I'm in Germany, the short date setting is set to this format: 09.01.2012

[NeoPa]

Ah so - That means you are likely to fall foul of the d/m order as well as the '.' char used in place of the '/'. You definitely need the updated form of the format string - "\#m\/d\/yyyy\#". This includes the hashes of course. If these are provided outside of the call to the Format() function then "m\/d\/yyyy" is enough.

Happy coding :-)