By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

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

Seth Schrock
Expert 2.5K+
P: 2,932
I have the following piece of code for my query. For some reason some of the records showup with the FileCount field having an error. When I click in the field it says "Data type mismatch in criteria expression." I assume that it is talking about the criteria in the DCount() function. The three fields that are referenced in the criteria portion of the DCount() function are as follows:
Expand|Select|Wrap|Line Numbers
  1. ACHID           AutoNumber
  2. EffectiveDate   Date 
  3. ACHCompanyID    Number
The following is the code.

Expand|Select|Wrap|Line Numbers
  1. SELECT   tblACHFiles.ACHID, 
  2.          DCount("*","[tblACHFiles]","[ACHID] <=" 
  3.          & [ACHID] 
  4.          & " AND Format([EffectiveDate],'yyyymm') =" 
  5.          & Format([EffectiveDate],'yyyymm') 
  6.          & " AND [ACHCompanyID] = " 
  7.          & [ACHCompanyID]) AS FileCount, 
  8.          Format([EffectiveDate],'yyyymm') AS YearMonth, 
  9.          tblACHFiles.EffectiveDate
  10. FROM     tblACHFiles
  11. WHERE    InvoiceID = 105
  12. ORDER BY Month(EffectiveDate), 
  13.          Day(EffectiveDate);
  14.  
What I don't understand is that about half of the records work and the other half don't. I have checked to make sure that the data in the table is the correct data type. I don't know where to go from here.
Jul 11 '12 #1
Share this Question
Share on Google+
44 Replies


zmbd
Expert Mod 5K+
P: 5,287
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):
Expand|Select|Wrap|Line Numbers
  1. (...) " AND Format([EffectiveDate],'yyyymm') =" 
  2. & Format( [EffectiveDate] ,'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
Jul 12 '12 #2

Seth Schrock
Expert 2.5K+
P: 2,932
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.
Jul 12 '12 #3

NeoPa
Expert Mod 15k+
P: 31,273
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.
Jul 12 '12 #4

Seth Schrock
Expert 2.5K+
P: 2,932
Sorry, I thought I was following those guidelines. Is the edited version better?
Jul 12 '12 #5

Seth Schrock
Expert 2.5K+
P: 2,932
Update: I've done some more testing and got the following results. I changed the following line of code (lines 2 -7 of OP):
Expand|Select|Wrap|Line Numbers
  1. DCount("*","[tblACHFiles]","[ACHID] <=" 
  2.          & [ACHID] 
  3.          & " AND Format([EffectiveDate],'yyyymm') =" 
  4.          & Format([EffectiveDate],'yyyymm') 
  5.          & " AND [ACHCompanyID] = " 
  6.          & [ACHCompanyID]) AS FileCount,
to the following:
Expand|Select|Wrap|Line Numbers
  1. DCount("*","[tblACHFiles]")
just to make sure that there were no problems there. It worked fine. I then added some of the criteria and had
Expand|Select|Wrap|Line Numbers
  1. DCount("*",[tblACHFiles]","[ACHID] <=" & [ACHID])
Again this worked fine. I then tried the following:
Expand|Select|Wrap|Line Numbers
  1. DCount("*","[tblACHFiles]","[ACHID] <=" 
  2. & [ACHID] 
  3. & " AND Format([EffectiveDate],'yyyymm') =" 
  4. & 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.
Jul 12 '12 #6

zmbd
Expert Mod 5K+
P: 5,287
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
Jul 12 '12 #7

NeoPa
Expert Mod 15k+
P: 31,273
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 :-)
Jul 12 '12 #8

Seth Schrock
Expert 2.5K+
P: 2,932
@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.
Jul 13 '12 #9

Seth Schrock
Expert 2.5K+
P: 2,932
Okay, I just tried the following for the DCount portion of the SQL and still no go.

Expand|Select|Wrap|Line Numbers
  1. DCount("*","[tblACHFiles]",("[ACHID] <=") 
  2. & [ACHID] 
  3. & (" AND Format([EffectiveDate],'yyyymm') =") 
  4. & (Format([EffectiveDate],'yyyymm')) 
  5. & (" AND [ACHCompanyID] = ") 
  6. & [ACHCompanyID]) AS FileCount
Attached is a screen shot of the results.
Attached Files
File Type: pdf Database Screen Shot.pdf (16.3 KB, 167 views)
Jul 13 '12 #10

zmbd
Expert Mod 5K+
P: 5,287
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Jul 13 '12 #11

Seth Schrock
Expert 2.5K+
P: 2,932
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 :)
Jul 13 '12 #12

zmbd
Expert Mod 5K+
P: 5,287
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.
Jul 13 '12 #13

Seth Schrock
Expert 2.5K+
P: 2,932
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
Expand|Select|Wrap|Line Numbers
  1. "YearMonth=" & YearMonth
(using the example mentioned earlier)?
Jul 13 '12 #14

zmbd
Expert Mod 5K+
P: 5,287
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
Jul 13 '12 #15

Seth Schrock
Expert 2.5K+
P: 2,932
Here is the thread from which I got the code that I'm using here: How do I count the number of entries in a certain month This might help you understand what I'm doing with the code. The code that I'm working on right now just focuses on the main fields are broken.

I've changed the order of the fields and got the same result. I'm working on getting the criteria changed to match the order you have. When you say "(( ... all numeric-data-type here... ) AND ( ... all date-data-type stuff here ... ))", do you mean that there would only be one instance of the AND operator or just that there would be an AND operator separating the numbers and the dates?

Sounds like your house is a zoo:)
Jul 13 '12 #16

zmbd
Expert Mod 5K+
P: 5,287
I'll look at the link here in a moment or two... if I can get them to stay down for a nap :)

"(( ... all numeric-data-type here... ) AND ( ... all date-data-type stuff here ... ))"

What I'm after is that on the left you have all of your logic comparing the numerics in that group ANDs and ORs etc...
Same thing on the right, in that case the dates... so you'd have:
Expand|Select|Wrap|Line Numbers
  1. (
  2. (numeric1 = numeric2 AND numeric3 = numeric4....) 
  3. AND
  4. (date1 = date2 AND date3 = date4.....)
  5. )
This way, all of the booooooolean hocus-pocus resolves in each group within the same data-type then the final comparison is between the boooooolean results (always reminds me of ghosts and and magic spells).

-z
Zoos don't let you drop off Two year olds... they make you take them with you when you leave... sigh :)

Let's just say... my life is not boring on my days off... just hope I don't turn them into psycotic... oh, they're two... too late. :)
It doesn't last forever... I just keep reminding myself of that... it helps... along with a Rosary or two or three...
Jul 13 '12 #17

NeoPa
Expert Mod 15k+
P: 31,273
I won't interrupt the flow Seth, but just popped in to say all is fine. I like your attitude.

I'm still confused, because the site shows me when a post was last edited - and according to that post #1 hasn't been since it was first submitted. That said, I doubt it's remotely important that I'm confused as all seems to be going along fine with you, zmbd and the zoo.

Good luck to all of you. It sounds like mayhem :-D
Jul 14 '12 #18

Seth Schrock
Expert 2.5K+
P: 2,932
Okay, I've tried the following, and there is no change in the results.

Expand|Select|Wrap|Line Numbers
  1. DCount("*",
  2. "[tblACHFiles]", 
  3. "((([ACHID] <="  & [ACHID]  & ") 
  4. AND ([ACHCompanyID] = " & [ACHCompanyID] &")) 
  5. AND ( Format([EffectiveDate],'yyyymm') ="  & 
  6. Format([EffectiveDate],'yyyymm')  & ") )") AS FileCount
Jul 16 '12 #19

zmbd
Expert Mod 5K+
P: 5,287
Ok,
I'm confused...

Let's just look at the criteria string

Because I'm getting lost in this code I'm going to break the string apart at the concatenation points:
Expand|Select|Wrap|Line Numbers
  1. "((( [ACHID] < = " &
  2. [ACHID] &
  3. ") AND ([ACHCompanyID] = " &
  4. [ACHCompanyID] &
  5. ")) AND (Format([EffectiveDate],'yyyymm') = " &
  6. Format([EffectiveDate],'yyyymm') &
  7. "))"
Ok, now, Lines 2, 4, and 6 have the evaluated data (data that either comes from the user, a form, or the table) and the remaining lines have your fixed criteria strings.

Lets use 1/1/2012 as a date... look at line 5 and 6... once evaluated we get (starting just after the AND):
Expand|Select|Wrap|Line Numbers
  1. (Format([EffectiveDate],'yyyymm') = 201201
I'm lost with this.
Jul 16 '12 #20

Seth Schrock
Expert 2.5K+
P: 2,932
I'll admit that I don't understand how the code works. Someone else created it for me. Each of the three fields referenced in the DCount command (ACHID, ACHCompanyID, & EffectiveDate) are fields in the table tblACHFiles. The only thing that I can think of is that Line 5 is finding all of the records that have the same yyyymm as the current record.
Jul 16 '12 #21

zmbd
Expert Mod 5K+
P: 5,287
Try the following - it worked in my test db without error - MSA-2010:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblACHFiles.ID,
  2.  tblACHFiles.ACHID,
  3.  tblACHFiles.ACHCompanyID,
  4.  tblACHFiles.EffectiveDate,
  5.  Format([EffectiveDate],'yyyymm') AS YearMonth,
  6.  DCount("*",
  7.     "[tblACHFiles]",
  8.     "(([ACHID]<= " & [ACHID] & ") AND ([ACHCompanyID] =" & [ACHCompanyID] & "))
  9.    AND
  10.     (( Format([effectivedate],'yyyymm')=" & Format([effectivedate],'yyyymm') & "))") AS FileCount
  11. FROM tblACHFiles;
I'll attach the test db in a little bit... which version of MS Access?
Jul 16 '12 #22

Seth Schrock
Expert 2.5K+
P: 2,932
I'm running Access 2010. Okay, the only thing that I had to change was to remove the tblACHFiles.ID as that isn't a field in my database. ACHID is my primary key. And, it still gives me errors on the same records. Is there a way that I can force it to make the data a date? I'm selecting the date from a date picker, but that doesn't seem to fix it. I just have a feeling that somehow it is the data being entered that makes it work on some records and not on others. The data type in the table is set as Date/Time for the EffectiveDate field.
Jul 16 '12 #23

zmbd
Expert Mod 5K+
P: 5,287
Not sure what the error is then...
Attached is the test db I've been working with...

and a screen shot for those that don't want to open and look:



-z
Attached Images
File Type: jpg typmismatch.jpg (54.2 KB, 12687 views)
Jul 16 '12 #24

zmbd
Expert Mod 5K+
P: 5,287
whew.... compressed the wrong database... thisone should be right...

doubled check... yep, that's a date field!

-z
{edit}2010-07-17:07h50; Removed the test database... see post 40 for a corrected code that has been null proofed.
-z{/edit}
Jul 16 '12 #25

Seth Schrock
Expert 2.5K+
P: 2,932
I don't get any errors with your database. I'm working on copying the data over to a brand new database and creating the query from scratch to see if there is something wrong with the database.
Jul 16 '12 #26

Seth Schrock
Expert 2.5K+
P: 2,932
I'm not sure what the difference is between our databases, but yours works and mine doesn't. I just copied the tables over to a new database and am going to try from scratch.

Edit: Please delete this post. I didn't see the other one posted when I posted this one.
Jul 16 '12 #27

Seth Schrock
Expert 2.5K+
P: 2,932
Is it possible that empty records being around the records that have errors could cause the error? I just tried starting from scratch and it didn't work. I then deleted the empty records and then it worked. I just want to see if you think that it was a coincidence before I try it on the live data.
Jul 16 '12 #28

zmbd
Expert Mod 5K+
P: 5,287
The function as given in your sql will not handle a null value in the [EffectiveDate] field; however, it should not cascade thru the entire query, it should just effect those [ACHCompanyID] that match the record wherein the null value is located {edit} and [ACHID]<= to that records [ACHID] value {/edit}.
-z
Jul 16 '12 #29

Seth Schrock
Expert 2.5K+
P: 2,932
I was meaning that the whole record was blank except for the ACHID (which was generated automatically). Would that throw off other records that did have all of the information?
Jul 16 '12 #30

Rabbit
Expert Mod 10K+
P: 12,327
@Seth, perhaps you could attach your database. Preferably in 2003 format so more people can look at it.
Jul 16 '12 #31

Seth Schrock
Expert 2.5K+
P: 2,932
I'll see what I can do. The database hold confidential customer information, so posting it would require me to replace all of the company TINs with fake numbers without breaking anything. I'll also see it will save easily in 2003 format. Some default settings for 2010 keep it certain things from converting to older version without a design change.
Jul 16 '12 #32

Seth Schrock
Expert 2.5K+
P: 2,932
I just took the plunge and deleted all of the records that were empty and it now works. I don't know what made me think of doing that, but I didn't think that the contents of one record would affect the others. Now I just need to figure out what needs done to keep empty records from being created.

Thanks to everyone that has jumped in here to help and especially to zmbd - I wish I could give you a best answer since you have spent so much time trying to help me.
Jul 16 '12 #33

zmbd
Expert Mod 5K+
P: 5,287
Taking the datbase I posted...
Duplicating the first 7 records into new records.
Deleteing the information other than that in the [ACHID] field.... I only get errors in those records. I also get the type mis-match pop and malfromed sql pop-up; however, that is when the [effectivedate] is nulled.
It doesn't cascade thru the query.
Jul 16 '12 #34

Seth Schrock
Expert 2.5K+
P: 2,932
For some reason the errors would cascade through some of the records. As you can see from the screen shot in post #10, the EffectiveDate was populated even for the records whose FileCount had the error. A bit of a mystery I guess.
Jul 16 '12 #35

zmbd
Expert Mod 5K+
P: 5,287
Post 10 does not show the [ACHCompanyID]; I would guess that it was blank as I can duplicate the errors as shown when I delete the [ACHCompanyID] values for [ACHID]=491,525,561,589,620,659,690,721.

We nolonger get the error you describe so there we have it... I'm off to a late lunch.
Jul 16 '12 #36

NeoPa
Expert Mod 15k+
P: 31,273
Seth, I normally use the following procedure when using any kind of complicated filtering (The WhereCriteria - or 3rd - parameter to DCount() is such an example) :
I create a string beforehand to pass to the function. This is very helpful when you have issues as :
  1. You can review the contents of the string before running the function while debugging.
  2. You can include the actual string value in a thread such as this and it will prove very helpful to those trying to assist you.

An example for your code might be :
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  2.  
  3. With Me
  4.     strWhere = "(([ACHID]<=%A) AND ([ACHCompanyID]=%C) AND " & _
  5.                "(Format([EffectiveDate],'yyyymm')='%E'))"
  6.     strWhere = Replace(strWhere, "%A", .ACHID)
  7.     strWhere = Replace(strWhere, "%C", .ACHCompanyID)
  8.     strWhere = Replace(strWhere, "%E", Format(.EffectiveDate, "yyyymm"))
  9. End With
Clearly, you have an extra, unnecessary pair of parentheses around the first two sets of criteria which will cause no issue, but you are also trying to compare a numeric string with a number, which will certainly cause an issue. Also, the SQL string refernce in the VBA will fail. I've resolved these obvious issues in your code in this example.

Let us know if this helps :-)

PS. It seems that a fair bit of water has flowed under the bridge since I last refereshed, so ignore this if it is no help.
Jul 17 '12 #37

Seth Schrock
Expert 2.5K+
P: 2,932
I will definitely keep that in mind when I do any complicated stuff in VBA, but I'm using DCount in SQL on this project. I wouldn't say that I have solved my issue here, but I did find a way to make it work. I deleted all of the intermittent empty records that only had the PK field populated. This fixed all of the errors that I was getting on the records that were populated. I don't know why, but since it works, I'm happy.
Jul 17 '12 #38

NeoPa
Expert Mod 15k+
P: 31,273
Seth Shrock:
I will definitely keep that in mind when I do any complicated stuff in VBA, but I'm using DCount in SQL on this project.
Indeed you are. That's exactly the point Seth. With this approach you can post the exact contents of the SQL-format string you are about to pass to the DCount() function, rather than simply showing the VBA code you THINK ought to result in something usable. In this case the original certainly wouldn't have ;-)

Now you've found something that works you needn't worry about it for this question, but I hope you understand how this can help you get answers much more quickly and easily in similar future situations (I can absolutely guarantee this thread would never have reached 38 posts had you posted this way initially). In many cases just taking this approach will be enough to show you the problem without even needing to post a question.
Jul 17 '12 #39

zmbd
Expert Mod 5K+
P: 5,287
The following code uses the IIF to null-proof the SQL,
It has been proofed in the test db and will return a 0 instead of the error (should have done this to begin with however, there was no mention that null values were allowed in the data at the time (post 9):
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.    tblACHFiles.ID, 
  3.    tblACHFiles.ACHID, 
  4.    tblACHFiles.ACHCompanyID, 
  5.    tblACHFiles.EffectiveDate, 
  6.    Format([EffectiveDate],'yyyymm') AS YearMonth, 
  7.    IIf(IsNull([ACHID]) Or 
  8.       IsNull([ACHCompanyID]) Or
  9.       IsNull([EffectiveDate]),
  10.          0, 
  11.             DCount("*","[tblACHFiles]","(([ACHID]<= " & [ACHID] & ") 
  12.                AND 
  13.               ([ACHCompanyID] =" & [ACHCompanyID] & "))
  14.                  AND
  15.                (( Format([effectivedate],'yyyymm')=" & Format([effectivedate],'yyyymm') & "))")) AS FileCount
  16. FROM tblACHFiles;
;-) NeoPa... ouch.... I did the best I could in 39 posts... that "Z" in the Avatar isn't the sound of a race engine more like a sleeping turtle, ( Z _ Z ) -zzzzzzzzzzzzzzz };-) However, that string replace is pretty slick... why didn't I think of that before... so obvious now that I see it! I'll be stealing that for my bag of tricks! - Thnx NeoPa!!!!
Jul 17 '12 #40

Seth Schrock
Expert 2.5K+
P: 2,932
I'll have to give that a try. Unfortunately, I have already deleted all of the empty records so I'll have to create some to test it. Thanks again.
Jul 17 '12 #41

NeoPa
Expert Mod 15k+
P: 31,273
zmbd:
;-) NeoPa... ouch.... I did the best I could in 39 posts... that "Z" in the Avatar isn't the sound of a race engine more like a sleeping turtle, ( Z _ Z ) -zzzzzzzzzzzzzzz
Z, That was not a criticism of your performance by any means (I suspect you know that). I was indicating to Seth that future threads won't be so hard for everyone to deal with if he uses the suggestion I gave about including that helpful extra information in the question. I have no doubt you would have reached a satisfactory resolution earlier than this had you that extra information available from the start. Everybody wins. Nor was I even criticising Seth. Just making a suggestion I believe he will find very beneficial going forward.

As for the laying out of the string, I find the concept so useful that I have a function I designed to support multiple pairs of replacement parameters. Actually, the earlier version was designed before I even knew of the Replace() function. It was so useful a function to have available. When I discovered the latter I updated the former to use it and thus run more efiiciently.
Expand|Select|Wrap|Line Numbers
  1. 'MultiReplace replaces all occurrences of varParam in strMain with varReplace.
  2. 'Using VbBinaryCompare means that case is not ignored.
  3. Public Function MultiReplace(ByRef strMain As String, _
  4.                              ByVal varParam As Variant, _
  5.                              ByVal varReplace As Variant, _
  6.                              ParamArray avarArgs()) As String
  7.     Dim intIdx As Integer
  8.  
  9.     If (UBound(avarArgs) - LBound(avarArgs)) Mod 2 = 0 Then Stop
  10.     MultiReplace = Replace(Expression:=strMain, _
  11.                            Find:=Nz(varParam, ""), _
  12.                            Replace:=Nz(varReplace, ""), _
  13.                            Compare:=vbBinaryCompare)
  14.     For intIdx = LBound(avarArgs) To UBound(avarArgs) Step 2
  15.         MultiReplace = Replace(Expression:=MultiReplace, _
  16.                                Find:=Nz(avarArgs(intIdx), ""), _
  17.                                Replace:=Nz(avarArgs(intIdx + 1), ""), _
  18.                                Compare:=vbBinaryCompare)
  19.     Next intIdx
  20. End Function
NB. This uses vbBinaryCompare so the comparisons are case-sensitive, which is not the default when using Replace().
Jul 18 '12 #42

zmbd
Expert Mod 5K+
P: 5,287
NeoPa.... you didn't see that :Twisted:
I did warn you that I have a dry sense of humor, no... };-)

Neat function... I'll be tearing it appart to understand it after we get back from vacation! Been coding strings the hardway for way too long and the blinders were on!

-z
Jul 18 '12 #43

NeoPa
Expert Mod 15k+
P: 31,273
zmbd:
NeoPa.... you didn't see that :Twisted:
I did warn you that I have a dry sense of humor, no... };-)
Of course I saw it Z. Unfortunately, I had no idea what it meant so walked into it somewhat maybe :-D

zmbd:
Neat function... I'll be tearing it appart to understand it after we get back from vacation! Been coding strings the hardway for way too long and the blinders were on!
Thanks. I'm sure you'll work it out in no time. It's pretty straightforward in that it simply repeats the replace function successively for each pair of parameters after the initial start-string. Be warned though, It checks the number of parameters passed and Stops if they don't balance. This is because any invalid call is a design issue and the programmer needs to ensure they don't happen. A normal user should never see that.
Jul 18 '12 #44

Seth Schrock
Expert 2.5K+
P: 2,932
After just coming across this problem again, I have finally figured out what the problem is and where the data type mismatch was.

From my first post, I had the following section of code:
Expand|Select|Wrap|Line Numbers
  1. DCount("*"
  2.     ,"[tblACHFiles]"
  3.     ,"[ACHID] <=" & [ACHID] 
  4.          & " AND Format([EffectiveDate],'yyyymm') = " & Format([EffectiveDate],'yyyymm') 
  5.          & " AND [ACHCompanyID] = " & [ACHCompanyID]) AS FileCount,
By running this through VBA, I was able to more easily figure out where the problem was coming from and it turns out that in the WHERE condition of the DCount function, the middle criteria was producing the error. I remembered that the Format() function returns a string value, so I tried placing single quotes around the result that I was comparing it to.
Expand|Select|Wrap|Line Numbers
  1.  & " AND Format([EffectiveDate],'yyyymm') = '" & Format([EffectiveDate],'yyyymm') & "'"
This fixed it.

What I really don't understand is that it worked for about 2/3 of the records in the same result set. Another way that I could fix it is to delete records that were blank except for the PK field (users would start a record and then change their mind and remove all the data that they entered and just leave the record blank). At least I have come up with a solution that doesn't include removing all blank records everytime I have to run this query.
Jan 8 '14 #45

Post your reply

Sign in to post your reply or Sign up for a free account.