OpenReport wont open what I need :(

[Toadums]

Ok...so this is gona be a hard one to explain...BUT DONT LEAVE

So I have a line:

DoCmd.OpenReport "rptsql", acViewReport, , criteria

and although the code is a lot more complex than this, i will just show you the part of it that is called (in this exact situation...when this elseif statement is called.)

So I have fields "Name" and "Date"...Name is just a string, and Date is formatted as "date/time"...

SO I would like my report to be opened with parameters: Name = "Paul" Date = 7/17/2009 and there is one record that fits that...

I think that the issue comes from the Date field being formatted as it is...if it was a string and I were looking for "7/17/2009", then it would be easier...but i am not sure if it is able to match up the date...?

anyways, have a look at the code, and let me know if you can find the source of the error

    ElseIf IsNull(Me.Text_ProjID) Then
        
        Dim val As String
        
        val = CDate(Me.ListBox1.Value)
                
        
        criteria = "Name = '" & Me.Text_Name.Column(1) & "' And Date =  " & val
        
         DoCmd.OpenReport "rptsql", acViewReport, , criteria

if i debug it, it says that criteria = "Name = 'Paul' And Date = 7/17/2009"

which is exactly what i want...not sure whats happening...Works fine if all that criteria = is "Name = 'Paul'", it is the Date that is messing it up..

let me know if you have any suggestions, thanks!!

 

[pbaldy]

Dates must be surrounded by #, in a similar manner that the text value is surrounded by '.

 

[Rich]

Also note that both Date and Name are reserved words in access and therefore shouldn't be used as field names

 

[namliam]

As rich said, they are reserved words... adding [] around both will ensure access usses the column names (or if possible, please change the column names!)

As Paul says... adding Hashes (#) around the date will help too

Finaly why use "val" at all? Your converting the date in the listbox to a real date (CDate), then implicitely converting again to a string... because Val is a string value....

What value does Me.ListBox1.Value contain?? If it actually hold the US format of MM/DD/YYYY then you should just be able to do so:
criteria = "[Name] = '" & Me.Text_Name.Column(1) & "' And [Date] = #" & Me.ListBox1.Value & "#"

Note:
.Value is the default for anything... so me.Listbox1 woudd work too
Note 2:
Listbox1, erm... Please use SENSIBLE names and dont leave the default names for controls. This goes to maintainability. Having some name like DateListbox or ListboxDate or lbxDate or something would be much easier to follow later on when you (or someone else) has to come back to revisit this (and you likely will have to revisit it someday...)

 

[Toadums]

perfect! thank you everyone for your help!!

I made all the changes you suggested namliam. it has been a while since I used access, I am a bit lost

but I think I am mostly back on track now