|
|
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 wrote:
[Quoted Text] >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?
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.
--
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.
hope this helps
"Marshall Barton" wrote:
[Quoted Text] > 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?
>
>
> 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.
>
> --
> Marsh
> MVP [MS Access]
>
|
|
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]
Momof2 wrote:
[Quoted Text] >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" wrote:
>> 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.
>
>> 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?
|
|
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" wrote:
[Quoted Text] > 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]
>
>
> Momof2 wrote:
> >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" wrote:
> >> 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.
>
> >
> >> 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?
>
|
|
Momof2 wrote:
[Quoted Text] >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.
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.
--
Marsh
MVP [MS Access]
|
|
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.
"Marshall Barton" wrote:
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" wrote:
[Quoted Text] > 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]
>
>
> Momof2 wrote:
> >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" wrote:
> >> 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.
>
> >
> >> 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?
>
|
|
Momof2 wrote:
[Quoted Text] >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.
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?
--
Marsh
MVP [MS Access]
|
|
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"
"Marshall Barton" Wrote:
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" wrote:
[Quoted Text] > 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]
>
>
> Momof2 wrote:
> >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" wrote:
> >> 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.
>
> >
> >> 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?
>
|
|
Momof2 wrote:
[Quoted Text] >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"
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.
--
Marsh
MVP [MS Access]
|
|
|