Why am I getting a data type mismatch in some of the query results?

**zmbd** · Jul 12 '12, 05:33 AM

Is the type assigned correctly?

Do I understand correctly, that this is code taken from a VBA module or form?

IIF this is correct, then the issue will more than likely be in how you are obtaining the comparison information. Thus, I would start with double checking that the comparison data is in the correct data-type.

Let me explain my thought there...

(because I think clearer with the code in-front of me I'll just pull a snip from your post):

Code:

(...) " AND Format([EffectiveDate],'yyyymm') =" 
& Format( [B][U][EffectiveDate] [/U][/B],'yyyymm')(...)

in line two, the underlined portion... from where is that information being taken?

If taken directly from a form text box control, then often it will be interpreted as a string (esp if delimited (one thousand being 1,000.00 or 1.000,00 depending on region). This is one reason I usually do not use the form control values directly in VBA, been burned here many times, instead I will define a variable of the correct data-type and then assign the value from the control to it, even going so-far as to wrap the control in one of the conversion functions such as
CINT(Me![SomeTxtBx])
when assigning to a variable dimensioned as an integer to insure that I get an integer value from the control. If you're taking this from an inputbox some how, then you might want to consider CLong() for your [EffectiveDate].

-z

**Seth Schrock** · Jul 12 '12, 11:07 AM

My code is in a query. The part that gets me is that in the same query execution, some of the records work and some don't. When I get to work, I'll send a print screen of the results.

**NeoPa** · Jul 12 '12, 12:04 PM

Seth, 286 posts and I haven't linked you to Before Posting (VBA or SQL) Code yet? I doubt it somehow ;-) Please follow the guidelines there when posting your questions, as otherwise you just make the whole situation so much more complicated to deal with.

**Seth Schrock** · Jul 12 '12, 12:39 PM

Sorry, I thought I was following those guidelines. Is the edited version better?

**Seth Schrock** · Jul 12 '12, 02:44 PM

Update: I've done some more testing and got the following results. I changed the following line of code (lines 2 -7 of OP):

Code:

DCount("*","[tblACHFiles]","[ACHID] <=" 
         & [ACHID] 
         & " AND Format([EffectiveDate],'yyyymm') =" 
         & Format([EffectiveDate],'yyyymm') 
         & " AND [ACHCompanyID] = " 
         & [ACHCompanyID]) AS FileCount,

to the following:

Code:

DCount("*","[tblACHFiles]")

just to make sure that there were no problems there. It worked fine. I then added some of the criteria and had

Code:

DCount("*",[tblACHFiles]","[ACHID] <=" & [ACHID])

Again this worked fine. I then tried the following:

Code:

DCount("*","[tblACHFiles]","[ACHID] <=" 
& [ACHID] 
& " AND Format([EffectiveDate],'yyyymm') =" 
& Format([EffectiveDate],'yyyymm'))

This time, I got the error. So I'm thinking that there is a problem in that field for some of the records. I tried re-entering the date on some of the records that had errored out, but no change.

**zmbd** · Jul 12 '12, 04:37 PM

Duh... That's what I get for reading code at 1am... I should have seen this to start with:

Could the SQL parser be getting lost in the criteria? If so then there might be a type mismatch between the [ACHID] and [EffectiveDate] as the parser gets confused... are all of the [ACHID] fields full of data? Are all of the [EffectiveDate} fields full of data?

In anycase, try enclosing the entire criteria inbetween "()" and "()" between the AND operators to help the parser group the criteria. I know that in a WHERE statement that this will often solve such an issue.

Sorry for being so turttle and mule about the question!

-z

**NeoPa** · Jul 12 '12, 11:58 PM

Originally posted by Seth

Seth:
Sorry, I thought I was following those guidelines. Is the edited version better?

I'm confused. It certainly seems like you've made an effort, but I'm looking at post #1 and it hasn't been edited, so maybe post #6 is what you're referring to. It's nicely formatted, but it seems to use DCount rather than a SELECT as used in post #1. DCount is VBA but SELECT is SQL, so there seems to be some confusion here.

All that said, it seems clear you're doing your best and that's all we ever need really :-)

**Seth Schrock** · Jul 13 '12, 12:39 PM

@zmbd I'm not sure if the SQL parser could be getting lost in the criteria. Both ACHID and EffectiveDate are populated in all records (I believe that I have thse as required fields). I'll try adding the "()" and let you know the results.

@NeoPa I changed the first post to make the code a little more neat. I'm using the DCount in SQL. The DCount in post #1 is in line 2. Post #6 is looking at just that field.

Always tell me if I'm not posting correctly. I want to make sure my posts are formatted as well as possible, both in code arrangement and wording.

**Seth Schrock** · Jul 13 '12, 12:59 PM

Okay, I just tried the following for the DCount portion of the SQL and still no go.

Code:

DCount("*","[tblACHFiles]",("[ACHID] <=") 
& [ACHID] 
& (" AND Format([EffectiveDate],'yyyymm') =") 
& (Format([EffectiveDate],'yyyymm')) 
& (" AND [ACHCompanyID] = ") 
& [ACHCompanyID]) AS FileCount

Attached is a screen shot of the results.

Attached Files

Database Screen Shot.pdf (16.3 KB, 346 views)

**zmbd** · Jul 13 '12, 02:34 PM

We'll I'm home with the twins :) today so this may not be my best work... I tend to get distracted, they're 2-1/2 and require a lot of love ;-)

I took a look, it seems as though the parenthesise are not quite what I was thinking so I tried a hand at sticking them in the places I thought they should go.

SO... I've left this on one line so that you should be able to Copy&Paste... normally I'd break this for ease of reading:

Code:

DCount("*","[tblACHFiles]", "(([ACHID] <="  & [ACHID]  & ") AND ( Format([EffectiveDate],'yyyymm') ="  & Format([EffectiveDate],'yyyymm')  & ") AND ([ACHCompanyID] = " & [ACHCompanyID] &"))") AS FileCount

I think I have this correct; however, I don't have the luxary of building a test database today... if it doesn't work or tosses an error at you I applogise in advance...

Hey, kids, don't pull the dog's tail... stop that... Daddy's typingg,, =dkaslre ahhhh save from the twins ( gota-luv-em)
:)

-z

**Seth Schrock** · Jul 13 '12, 02:48 PM

There weren't any errors in the code, but I still get the same results. If you need more information, just ask. I've done all the troubleshooting that I know of and given the results here, so I don't know what other information might be useful to you.

Tells the twins hi for me :)

**zmbd** · Jul 13 '12, 03:02 PM

darn... :(

I think that you're were on the correct troubleshooting path by breaking the code down.

What I think I would do now is take each of your 'yyyymm' formatted [EffectiveDate] within the DCOUNT() and place them in there own fields. Then do a comparison between the fields.... clear as mud?

- OK, said Hi to the twins for you. Twins were in the process of destroying the bathroom... I have 9yr DD that forgets to close the door... then the twins... Bathrooms + Twins = Natural Disaster of Biblical proportions! I must now go fish the Egyptians out of the tub, put the potty back on the hole, and mop up what left of the Nile on the Bathroom floor.

**Seth Schrock** · Jul 13 '12, 03:21 PM

I think I know what you mean by placing them in their own fields (example line 8 of OP), but I'm not sure of how to compare them differently then they are being compare right now. Do you mean

Code:

"YearMonth=" & YearMonth

(using the example mentioned earlier)?

**zmbd** · Jul 13 '12, 05:03 PM

Red Herring... but I like my Herring pickled!

I think I might have taken the wrong fork, I've been focusing on the DCOUNT issue...

so the following may be a red-herring:

I took a look at pdf you posted in 10: the order of the fields is:
[ACHID];[FileCount];[YearMonth];[EffectiveDate]

I double checked your OP SQL and the two jogged my memory about a situation where I was using results from one calculated field within another... and would occasionally get these strange errors. Solved it by accident when I rebuilt the query from scratch... that was like... 10 years ago so I had forgotten about the situation.

Switch the order so that we now have:
[ACHID];[EffectiveDate];[YearMonth];[FileCount]

Ok... why I think it worked in my query is that I suspect that MSA reads left to right. So by having the source fields first, then the results from any calculated fields used in subsequent fields, then that information is available for the following fields.

and you tie your shoes by making an "X" with the string...

SO we now have the root data first: [ACHID];[EffectiveDate];
Then the first calculted field: [YearMonth];
and then finally the field that uses all of the afor mentioned information: [FileCount]

The field order might still be a red-herring.

and yet I like Pickled Herring:

I am solidly convinced and I'm still thinking that it has to do with the AND comparision having numeric-type in the [ACHID] and date-type in [EffectiveDate].
If so, then we need to group so that we have
"(( ... all numeric-data-type here... ) AND ( ... all date-data-type stuff here ... ))"
that way we're comparing the boolean results between the two data-types and the entire comparison is enclosed into itself.

Just finished rebuilding Rome, need to go harvest a field or two to feed the kids... maybe I can get them to finish tying their shoes... (ok... now the rabbit runs around the tree and jumps thru the hole... no, thru the hole, no... hold the tree... the bunny runs... OK, start back at the "X"...)

-z

Why am I getting a data type mismatch in some of the query results?

Why am I getting a data type mismatch in some of the query results?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment