Data mismatch

[cbende2]

Hello all,

I'm getting a data mismatch on this line of code:

= DCount("[5500Reviewed]", "[AnnualReportDate5500RevAll]", "DateValue([5500Reviewed]) Between #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + (i * 7) & "# And #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + 6 + (i * 7) & "#")

The field [5500Reviewed] is a text field, and [AnnualReportDate5500RevAll] is a query.

If I run the query by itself it returns fine. However when I run with the code, it is saying data mismatch.

I have 5 other statements almost exactly the same and all are working perfect.


Thanks

[June7]

Where is this expression? In criteria row of query object? The i variable has no meaning in a query object or textbox ControlSource.

[cbende2]

Its in a Form_Load() event, here's the For statement:

For i = 0 To 51

    O(6, i + 1) = DCount("[5500Reviewed]", "AnnualReportDate5500Rev1", "DateValue([5500Reviewed]) Between #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + (i * 7) & "# And #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + 6 + (i * 7) & "#")
    Me.Controls("6Text" & (i + 1)).Value = O(6, i + 1)


Next i

[June7]

The left side of = sign makes no sense. The left side must be a variable (or field or control) that will be set to the result of the right side.

[cbende2]

Here is all of my code, hopefully this will help:

Private Sub Form_Load()

Dim O(6, 52) As Integer
Dim i As Integer
Dim k As Integer
Dim j As Integer
Dim SumField1 As Integer
Dim SumField2 As Integer
Dim SumField3 As Integer
Dim SumField4 As Integer
Dim SumField5 As Integer
Dim SumField6 As Integer


For i = 0 To 51


    O(1, i + 1) = DCount("[CensusRecv'd]", "AnnualReportCensusAll", "DateValue([CensusRecv'd]) Between #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + (i * 7) & "# And #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + 6 + (i * 7) & "#")
    Me.Controls("Text" & (i + 1)).Value = O(1, i + 1)


Next i
For k = 0 To 51
    SumField1 = SumField1 + Me.Controls("Text" & (k + 1)).Value
Next k
Me.Count1.Value = SumField1




For i = 0 To 51


    O(2, i + 1) = DCount("DateContributionCalculated", "AnnualReportDateContCalcAll", "DateValue([DateContributionCalculated]) Between #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + (i * 7) & "# And #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + 6 + (i * 7) & "#")
    Me.Controls("2Text" & (i + 1)).Value = O(2, i + 1)


Next i
For k = 0 To 51
    SumField2 = SumField2 + Me.Controls("2Text" & (k + 1)).Value
Next k
Me.Count2.Value = SumField2




For i = 0 To 51


    O(3, i + 1) = DCount("DateTestCompleted", "AnnualReportDateTestComplAll", "DateValue([DateTestCompleted]) Between #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + (i * 7) & "# And #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + 6 + (i * 7) & "#")
    Me.Controls("3Text" & (i + 1)).Value = O(3, i + 1)


Next i
For k = 0 To 51
    SumField3 = SumField3 + Me.Controls("3Text" & (k + 1)).Value
Next k
Me.Count3.Value = SumField3




For i = 0 To 51


    O(4, i + 1) = DCount("Date5500Completed", "AnnualReportDate5500ComplAll", "DateValue([Date5500Completed]) Between #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + (i * 7) & "# And #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + 6 + (i * 7) & "#")
    Me.Controls("4Text" & (i + 1)).Value = O(4, i + 1)


Next i
For k = 0 To 51
    SumField4 = SumField4 + Me.Controls("4Text" & (k + 1)).Value
Next k
Me.Count4.Value = SumField4




For i = 0 To 51


    O(5, i + 1) = DCount("DateTestReviewed", "AnnualReportDateTestReviewedAll", "DateValue([DateTestReviewed]) Between #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + (i * 7) & "# And #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + 6 + (i * 7) & "#")
    Me.Controls("5Text" & (i + 1)).Value = O(5, i + 1)


Next i
For k = 0 To 51
    SumField5 = SumField5 + Me.Controls("5Text" & (k + 1)).Value
Next k
Me.Count5.Value = SumField5




For i = 0 To 51


    O(6, i + 1) = DCount("[5500Reviewed]", "AnnualReportDate5500Rev1", "DateValue([5500Reviewed]) Between #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + (i * 7) & "# And #" & DateSerial(Forms!AnnualReportsMenu!txtYear, 1, 1) + 6 + (i * 7) & "#")
    Me.Controls("6Text" & (i + 1)).Value = O(6, i + 1)
O(6, i + 1) = i
Next i
For k = 0 To 51
    SumField6 = SumField6 + Me.Controls("6Text" & (k + 1)).Value
Next k
Me.Count6.Value = SumField6


'sum of Fields'








End Sub

Dim(6,i) is the only one giving me a problem, stating that I have a data mismatch.

[June7]

Okay, that is a 2D array declared as integer type. Sorry, should have recognized array construct.

Have you declared Option Base 1 in the module header?

I can't see anything wrong with code. What is value returned by DCount when the error triggers?

[pbaldy]

I mentioned in your other thread that the DateValue() function would throw that error if it encounters a text value.

[June7]

And I suggested in that thread an IIf() expression to handle possible text, non-date values.

[IrogSinta]

Wow, over 300 DCounts in a single procedure! That's incredibly inefficient. Aggregate functions such as DCounts, DLookups, etc, should be used sparingly. It would be far much better to loop through a recordset based on your query. Faster still to use summation query. Are the 6 different queries you have all based on the same table? If you post the SQL strings of your queries, we may be able to give you better suggestions.

[cbende2]

So the problem was not with the code, there was a record that was like this: 0523/2015

It was missing the first slash.

And I agree, this is very inefficient. I am looping through queries instead of the entire table.

Yes all 6 different queries are based on the same table.

Is there any criteria for a query that only pulls records that meet this format: mm/dd/yyyy?

If I could put that in my query criteria, then it doesn't really matter if its a text field or not. It will only be pulling text with a date format which is all I'm really looking for.

Thanks for the help guys.

[June7]

Try IsDate() function - example was offered in your other thread.

[cbende2]

Yep, it's working perfect now, only just as IrogSinta said, incredibly inefficient.

[IrogSinta]

Why not post the SQL string of a couple of your queries. Maybe we could provide you with a better solution.

[cbende2]

The queries are extremely simple.

Selecting 2 fields, my primary key field and my short text field. And I also have Expr: IsDate(MyShortTextField).

So only 3 fields really. No criteria. All criteria is in the code.


When running on the network backend it took a whopping 15 minutes to load the form. However, when I switched the backend over to SQL Server it took about 5 seconds to load.

I'm currently in the process of experimenting with SQL Server, and it looks like that's the direction the company wants to go in.