Coding help run-time error '3464'

  • Thread starter Thread starter Momof2
  • Start date Start date
M

Momof2

I have a form with 3 combo boxes in which I want to use to pull information
from a report. Orginially I had only one combo box in which I was able to
get to work using the following code.
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

then I added to other combo boxes and tried using the following code
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """" And [Statement] = """_
& Me.Combo2 & """ And [Card] = """ & Me.Combo4 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

Now I keep getting an error message Run-time error '3464' Data Type mismatch
in criteria experssion. Any ideas on how to make this work?
 
Momof2 said:
I have a form with 3 combo boxes in which I want to use to pull information
from a report. Orginially I had only one combo box in which I was able to
get to work using the following code.
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

then I added to other combo boxes and tried using the following code
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """" And [Statement] = """_
& Me.Combo2 & """ And [Card] = """ & Me.Combo4 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

Now I keep getting an error message Run-time error '3464' Data Type mismatch
in criteria experssion. Any ideas on how to make this work?
Click to expand...


Your quotes are out of whack. And, if any of those fields
are a numeric type, you should not have quotes around the
value.

Even the original expression that you said works has the
quotes wrong, so I am confused about what you posted. If
you retyped the code into your post, repost using Copy/Paste
instead of retyping so we don't waste time going back and
forth discussing posting typos. Be sure to include the data
types of the Contractor, Statement and Card fields in the
table in your reply.
 
I did copy/paste the code that was used in VBA. Contractor data type is
lastname, First name. Statement data type is (mm/dd/yy) and card data type is
(ex: visa, wachovia, Amex). The orginal code still works if I remove the
statement and card.

hope this helps

Marshall Barton said:
Momof2 said:
I have a form with 3 combo boxes in which I want to use to pull information
from a report. Orginially I had only one combo box in which I was able to
get to work using the following code.
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

then I added to other combo boxes and tried using the following code
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """" And [Statement] = """_
& Me.Combo2 & """ And [Card] = """ & Me.Combo4 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

Now I keep getting an error message Run-time error '3464' Data Type mismatch
in criteria experssion. Any ideas on how to make this work?
Click to expand...


Your quotes are out of whack. And, if any of those fields
are a numeric type, you should not have quotes around the
value.

Even the original expression that you said works has the
quotes wrong, so I am confused about what you posted. If
you retyped the code into your post, repost using Copy/Paste
instead of retyping so we don't waste time going back and
forth discussing posting typos. Be sure to include the data
types of the Contractor, Statement and Card fields in the
table in your reply.
Click to expand...
 
Those are not field data types. They're more like examples
or formats. You need to look at the table in design view to
see the data types. I think, but can not be sure, that the
data types are:

Contractor Text
Statement Date (could be Text??)
Card Text

You should have a compile error or different code for your
original code. It should be:

StrWhere = "[Contractor] = """ & Me.Combo0 & """"

That's 1, 3, 4 quotes, not 1, 3, 5

Assuming my guess at the field data types is correct, the
new expression could be more like:

StrWhere = "Contractor = """ & Me.Combo0 _
& """ And Statement = " _
& Format(Me.Combo2, "\#yyyy-m-d\#") _
& " And Card = """ & Me.Combo4 & """"
Debug.Print StrWhere

Be sure to count the quotes carefully.

The Debug.Print is just there for testing so you can see the
results of the expression in the Immediate window, Remove
or comment it out after you get all this working.
--
Marsh
MVP [MS Access]

I did copy/paste the code that was used in VBA. Contractor data type is
lastname, First name. Statement data type is (mm/dd/yy) and card data type is
(ex: visa, wachovia, Amex). The orginal code still works if I remove the
statement and card.

Marshall Barton said:
Your quotes are out of whack. And, if any of those fields
are a numeric type, you should not have quotes around the
value.

Even the original expression that you said works has the
quotes wrong, so I am confused about what you posted. If
you retyped the code into your post, repost using Copy/Paste
instead of retyping so we don't waste time going back and
forth discussing posting typos. Be sure to include the data
types of the Contractor, Statement and Card fields in the
table in your reply.
Click to expand...
Momof2 said:
I have a form with 3 combo boxes in which I want to use to pull information
from a report. Orginially I had only one combo box in which I was able to
get to work using the following code.
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

then I added to other combo boxes and tried using the following code
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """" And [Statement] = """_
& Me.Combo2 & """ And [Card] = """ & Me.Combo4 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

Now I keep getting an error message Run-time error '3464' Data Type mismatch
in criteria experssion. Any ideas on how to make this work?
Click to expand...
Click to expand...
Click to expand...
 
Sorry I'm new to access.
Contractor TEXT
Statement Date/Time (mm/dd/yyyy format)
Card TEXT

I tried the expression below, it opens the report however the report shows
up blank with an "error" where the statement date is on the report. I even
tried ultering your expression chaning to the mm/dd/yyyy format and am still
getting the same error. Any ideas?

Marshall Barton said:
Those are not field data types. They're more like examples
or formats. You need to look at the table in design view to
see the data types. I think, but can not be sure, that the
data types are:

Contractor Text
Statement Date (could be Text??)
Card Text

You should have a compile error or different code for your
original code. It should be:

StrWhere = "[Contractor] = """ & Me.Combo0 & """"

That's 1, 3, 4 quotes, not 1, 3, 5

Assuming my guess at the field data types is correct, the
new expression could be more like:

StrWhere = "Contractor = """ & Me.Combo0 _
& """ And Statement = " _
& Format(Me.Combo2, "\#yyyy-m-d\#") _
& " And Card = """ & Me.Combo4 & """"
Debug.Print StrWhere

Be sure to count the quotes carefully.

The Debug.Print is just there for testing so you can see the
results of the expression in the Immediate window, Remove
or comment it out after you get all this working.
--
Marsh
MVP [MS Access]

I did copy/paste the code that was used in VBA. Contractor data type is
lastname, First name. Statement data type is (mm/dd/yy) and card data type is
(ex: visa, wachovia, Amex). The orginal code still works if I remove the
statement and card.

Marshall Barton said:
Your quotes are out of whack. And, if any of those fields
are a numeric type, you should not have quotes around the
value.

Even the original expression that you said works has the
quotes wrong, so I am confused about what you posted. If
you retyped the code into your post, repost using Copy/Paste
instead of retyping so we don't waste time going back and
forth discussing posting typos. Be sure to include the data
types of the Contractor, Statement and Card fields in the
table in your reply.
Click to expand...
Momof2 wrote:
I have a form with 3 combo boxes in which I want to use to pull information
from a report. Orginially I had only one combo box in which I was able to
get to work using the following code.
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

then I added to other combo boxes and tried using the following code
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """" And [Statement] = """_
& Me.Combo2 & """ And [Card] = """ & Me.Combo4 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

Now I keep getting an error message Run-time error '3464' Data Type mismatch
in criteria experssion. Any ideas on how to make this work?
Click to expand...
Click to expand...
Click to expand...
 
Momof2 said:
Sorry I'm new to access.
Contractor TEXT
Statement Date/Time (mm/dd/yyyy format)
Card TEXT

I tried the expression below, it opens the report however the report shows
up blank with an "error" where the statement date is on the report. I even
tried ultering your expression chaning to the mm/dd/yyyy format and am still
getting the same error.
Click to expand...


Please post what you actually used along with the report's
record source query. If the report's record source is a
table, list the fields and their data type.

A blank report with #Error in the bound text boxes strongly
implies that there are no records that match the conditions
in the strWhere string.

One common reason for that happening when everything "looks"
like it should work is the use of Lookup fields in the
table's design. Be sure to check for these things and
explain the details of any that you find.
 
This is what I actually used
Dim StrWhere As String
StrWhere = "Contractor = """ & Me.Combo0 _
& """ And Statement = " _
& Format(Me.Combo2, "\#mm/dd/yyyy\#") _
& " And Card = """ & Me.Combo4 & """"
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

The reports record source is a coding query with the following fields
Contractor = Text
Statement = Date
Card = Text
Charge Code = Text
Charge Description = Text
Expense Code = Text
Expense Description = Text
Amount = Currency

The report is grouped by
Contractor
Card
Statement
Charge Code, Charge Descrip, Expense Code,
Expense Descrip, Amount.

The combo boxes in which I'm using on my form are bound to the same query,
but using a Select Distinct SQL. Hope this helps.


:
Please post what you actually used along with the report's
record source query. If the report's record source is a
table, list the fields and their data type.

A blank report with #Error in the bound text boxes strongly
implies that there are no records that match the conditions
in the strWhere string.

One common reason for that happening when everything "looks"
like it should work is the use of Lookup fields in the
table's design. Be sure to check for these things and
explain the details of any that you find.


Marshall Barton said:
Those are not field data types. They're more like examples
or formats. You need to look at the table in design view to
see the data types. I think, but can not be sure, that the
data types are:

Contractor Text
Statement Date (could be Text??)
Card Text

You should have a compile error or different code for your
original code. It should be:

StrWhere = "[Contractor] = """ & Me.Combo0 & """"

That's 1, 3, 4 quotes, not 1, 3, 5

Assuming my guess at the field data types is correct, the
new expression could be more like:

StrWhere = "Contractor = """ & Me.Combo0 _
& """ And Statement = " _
& Format(Me.Combo2, "\#yyyy-m-d\#") _
& " And Card = """ & Me.Combo4 & """"
Debug.Print StrWhere

Be sure to count the quotes carefully.

The Debug.Print is just there for testing so you can see the
results of the expression in the Immediate window, Remove
or comment it out after you get all this working.
--
Marsh
MVP [MS Access]

I did copy/paste the code that was used in VBA. Contractor data type is
lastname, First name. Statement data type is (mm/dd/yy) and card data type is
(ex: visa, wachovia, Amex). The orginal code still works if I remove the
statement and card.

Marshall Barton said:
Your quotes are out of whack. And, if any of those fields
are a numeric type, you should not have quotes around the
value.

Even the original expression that you said works has the
quotes wrong, so I am confused about what you posted. If
you retyped the code into your post, repost using Copy/Paste
instead of retyping so we don't waste time going back and
forth discussing posting typos. Be sure to include the data
types of the Contractor, Statement and Card fields in the
table in your reply.
Click to expand...
Momof2 wrote:
I have a form with 3 combo boxes in which I want to use to pull information
from a report. Orginially I had only one combo box in which I was able to
get to work using the following code.
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

then I added to other combo boxes and tried using the following code
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """" And [Statement] = """_
& Me.Combo2 & """ And [Card] = """ & Me.Combo4 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

Now I keep getting an error message Run-time error '3464' Data Type mismatch
in criteria experssion. Any ideas on how to make this work?
Click to expand...
Click to expand...
Click to expand...
 
Momof2 said:
This is what I actually used
Dim StrWhere As String
StrWhere = "Contractor = """ & Me.Combo0 _
& """ And Statement = " _
& Format(Me.Combo2, "\#mm/dd/yyyy\#") _
& " And Card = """ & Me.Combo4 & """"
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

The reports record source is a coding query with the following fields
Contractor = Text
Statement = Date
Card = Text
Charge Code = Text
Charge Description = Text
Expense Code = Text
Expense Description = Text
Amount = Currency

The report is grouped by
Contractor
Card
Statement
Charge Code, Charge Descrip, Expense Code,
Expense Descrip, Amount.

The combo boxes in which I'm using on my form are bound to the same query,
but using a Select Distinct SQL.
Click to expand...


The report looks straightforward enough. You did say it
works fine with just your original code, right?

The query looks OK, but all bets are off if there are any
Lookup fields in there.

A single SELECT DISTINCT query based on the report's record
source query is unlikely to be useful for three different
combo boxes. You are probably using three different SELECT
DISTINCT queries. Either way, I need to check the queries
along with the comboxes' BoundColumn property. More details
please.

What did you find when you checked the table for Lookup
fields?

What did the Debug.Print display in the Immediate window?
 
Yes it does work with the original code when I am only looking up the
contractor. Its when I add the statement and card that throws it off.

I looked in the query and there are no lookup fields there. and you are
correct I am using three Select Distinct Queries based off the original
table.

this is what I get in the immediate window
Contractor = "Smith, Brian" And Statement = #11/22/2008# And Card = "WACHOVIA"



:

The report looks straightforward enough. You did say it
works fine with just your original code, right?

The query looks OK, but all bets are off if there are any
Lookup fields in there.

A single SELECT DISTINCT query based on the report's record
source query is unlikely to be useful for three different
combo boxes. You are probably using three different SELECT
DISTINCT queries. Either way, I need to check the queries
along with the comboxes' BoundColumn property. More details
please.

What did you find when you checked the table for Lookup
fields?

What did the Debug.Print display in the Immediate window?

Marshall Barton said:
Those are not field data types. They're more like examples
or formats. You need to look at the table in design view to
see the data types. I think, but can not be sure, that the
data types are:

Contractor Text
Statement Date (could be Text??)
Card Text

You should have a compile error or different code for your
original code. It should be:

StrWhere = "[Contractor] = """ & Me.Combo0 & """"

That's 1, 3, 4 quotes, not 1, 3, 5

Assuming my guess at the field data types is correct, the
new expression could be more like:

StrWhere = "Contractor = """ & Me.Combo0 _
& """ And Statement = " _
& Format(Me.Combo2, "\#yyyy-m-d\#") _
& " And Card = """ & Me.Combo4 & """"
Debug.Print StrWhere

Be sure to count the quotes carefully.

The Debug.Print is just there for testing so you can see the
results of the expression in the Immediate window, Remove
or comment it out after you get all this working.
--
Marsh
MVP [MS Access]

I did copy/paste the code that was used in VBA. Contractor data type is
lastname, First name. Statement data type is (mm/dd/yy) and card data type is
(ex: visa, wachovia, Amex). The orginal code still works if I remove the
statement and card.

Marshall Barton said:
Your quotes are out of whack. And, if any of those fields
are a numeric type, you should not have quotes around the
value.

Even the original expression that you said works has the
quotes wrong, so I am confused about what you posted. If
you retyped the code into your post, repost using Copy/Paste
instead of retyping so we don't waste time going back and
forth discussing posting typos. Be sure to include the data
types of the Contractor, Statement and Card fields in the
table in your reply.
Click to expand...
Momof2 wrote:
I have a form with 3 combo boxes in which I want to use to pull information
from a report. Orginially I had only one combo box in which I was able to
get to work using the following code.
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

then I added to other combo boxes and tried using the following code
Dim StrWhere As String
StrWhere = "[Contractor] = """ & Me.Combo0 & """" And [Statement] = """_
& Me.Combo2 & """ And [Card] = """ & Me.Combo4 & """""
DoCmd.OpenReport "coding", acViewPreview, , StrWhere

Now I keep getting an error message Run-time error '3464' Data Type mismatch
in criteria experssion. Any ideas on how to make this work?
Click to expand...
Click to expand...
Click to expand...
 
Momof2 said:
Yes it does work with the original code when I am only looking up the
contractor. Its when I add the statement and card that throws it off.

I looked in the query and there are no lookup fields there. and you are
correct I am using three Select Distinct Queries based off the original
table.

this is what I get in the immediate window
Contractor = "Smith, Brian" And Statement = #11/22/2008# And Card = "WACHOVIA"
Click to expand...


Good, the where string looks like it makes sense.

With this information, it looks even more likely that the
Card field is a Lookup field. I suspect the you have a
table of cards with an ID field and a name field. If the
***table*** your query is based on has the Card field's
Lookup set to a combo box with its BoundColumn set to the ID
and the visible column is the card name, then everything
will look sensible, but what you see is **not** what's
really there. If the Card field does have it Lookup set to
combo box, change it back to text box so you can see the
real value. If that is indeed what's going on, you can fix
the problem by making the card combo box on the form do what
the combo box in the table was doing (i.e. use cards table
as the row source with the BoundColumn set to the ID field).

It doesn't seem likely, but if you have a statements table,
then it is possible that you could have a similar scenario
for the statement field.

If none of that speculation pans out, then look at the data
in the table and make sure that the Statement field does not
have a time part. You should clear the field's Format
property (in the table) so you can see what's really there.
If there is a date part in the Statement field, the fix
would depend on how you want to select the desired card
value.
 
Back
Top