Help with a type mismatch error 1

[stevedemo]

I have tried the following pieces of code and all produce an error. I am stumped.


Dim OntimeString, startdate, enddate As String
reportname = "database_Query_Report_by_date2"

'Error Type Mismatch
OntimeString = "[Date Only] >=" + CDate(Text0.Value) + " and [Date Only] <=" + CDate(Text2.Value)

DoCmd.OpenReport reportname, acViewPreview, , OntimeString



Dim OntimeString, startdate, enddate As String
reportname = "database_Query_Report_by_date2"

'Error Type Mismatch
OntimeString = "[Date Only] >=" + Text0.Value + " and [Date Only] <=" + Text2.Value
DoCmd.OpenReport reportname, acViewPreview, , OntimeString



Dim OntimeString, startdate, enddate As String
reportname = "database_Query_Report_by_date2"
startdate = Text0.value
enddate = Text2.value

'Error Type Mismatch
OntimeString = "[Date Only] >=" + startdate + " and [Date Only] <=" + enddate
DoCmd.OpenReport reportname, acViewPreview, , OntimeString


Any Suggestions ?



All I want is the chance to prove money won't make me happy.

 

[grgimpy]

Dim OntimeString as String
Dim reportname as String
reportname = "database_Query_Report_by_date2"

'Error Type Mismatch
OntimeString = "[Date Only] >=#" & CDate(Text0.Value) & "#  and [Date Only] <=#" & CDate(Text2.Value) & "#"

DoCmd.OpenReport reportname, acViewPreview, , OntimeString

When you Dim variables, you have to declare each variable type separately (dim x as string, y as string, etc not dim x,y,z as string).

Also, you should use the & symbol to concatenate, not + because it can screw you up in certain cases.

I think the reason you got the error was because the text box values were dates, and you must surround dates with the # sign when filtering the data.

Hope this helps.

 

[stevedemo]

Thanks grgimpy !!!

I made your suggestions and it works like a charm.

Dim OntimeString As String, startdate As String, enddate As String

reportname = "database_Query_Report_needed_by_date2"

OntimeString = "[Date Only] >= #" & Text0.Value & "#  and [Date Only] <= #" & Text2.Value & "#"

DoCmd.OpenReport reportname, acViewPreview, , OntimeString

All I want is the chance to prove money won't make me happy.

 

[missinglinq]

When you Dim variables, you have to declare each variable type separately (dim x as string, y as string, etc not dim x,y,z as string)"

This is simply not true!


The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[PHV]

Another way:
OntimeString = "[Date Only] Between #" & Text0.Value & "# And #" & Text2.Value & "#"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[grgimpy]

if you do not declare each variable separately then

dim x,y,z as string

is actually translated as

dim x as variant, y as variant, z as string

maybe i was not clear in my explanation but please tell me if this example is incorrect.

 

[missinglinq]

The line

Dim x,y,z

dims x, y, z as Variant

the line

Dim x,y,z as String

dims x, y, z as Strings

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[grgimpy]

you are wrong missinglinq unless something has changed in recent updates to VBA.

http://support.microsoft.com/kb/212673/en-us

 

[missinglinq]

Although the reference you cited is for Word 2000, I suspect you're right! I stand corrected.

Thanks!

Linq

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[jrbarnett]

missinglinq - proof:

Sub test1()
    
    Dim x, y, z As String
    
    Debug.Print "X: Variable type: " & VarType(x)
    Debug.Print "Y: Variable type: " & VarType(y)
    Debug.Print "Z: Variable type: " & VarType(z)
    
End Sub

Hit F1 on VarType to find out the descriptive variable types for the numbers shown. You need the VBA help installed (but I'd guess you would have that as a VBA developer anyway)

John