Using the Build Criteria - Datatype Mismatch (Access 2k, DAO)

[Leonard Priestley]

I hope someone can help me spot the problem which has taken heaps of time,
and is causing much despair.

I am creating a database to track quality assurance testing on equipment on
two sites. Six months, beginning in April, is spent on one site, then
testing commences on the second site in October. I want to produce reports
covering work done each month, and also cumulative reports which cover from
the beginning of the test period to the current month, inclusive. In this
case, specifying the site of interest automatically enables me to also
specify the first month of the period for a cumulative report.

In the past I have used the BuildCriteria Method to pick up values from
controls on a main report sheet and produce a Where clause, to be used in a
line of the form:
DoCmd.OpenReport strDocName, acPreview, , strWhereClause

A report for a particular month works fine. The code to produce the
criteria looks like this (I have left some bits out for simplicity):

strWhereClause = "(" & BuildCriteria("SiteID", dbLong,
Form_frmReportMain.cboSite) & ")"
'Gives the site
strWhereClause = strWhereClause & " AND "
'concatenated with
strWhereClause = strWhereClause & "(" & BuildCriteria("MonthNotFound",
dbLong, Form_frmReportMain.cboMonth) & ")" 'the month of interest

( Interestingly, this works OK if I specify the datatype dbInteger instead
of dbLong)

My problem has arisen when I try to construct criteria for a cumulative
report:

I begin by specifying the site again, and then I use a few lines of code to
get the number 4 or 10 (that is, the months April or October) and place this
in a
textbox (txtBegin) on frmReportMain, to be picked up by the BuildCriteria
code. The code I use to build a criterion for the period between the
beginning of the test period and the month of interest is:

Dim intStartMonth As Integer
Dim intThisMonth As Integer

intStartMonth = Form_frmReportMain.[txtBegin]
intThisMonth = Form_frmReportMain.[cboMonth]

strWhereClause = strWhereClause & "(" & BuildCriteria("MonthNotFound",
dbInteger, "Between intStartMonth and intThisMonth") & ")"

This produces a WhereClause which the DoCmd.OpenReport code will not accept.
I keep getting a message "Datatype mismatch in criteria expression"

I have tried redefining variables as integers or long integers. I have
tried changing the datatypes in the BuildCriteria expression . I have
checked what happens at various point in the program, and I can't seem to
sort this out.
If I use a messagebox to show what happens after settig the values of
intStartMonth and intThisMonth, I get the correct values. If I replace
these variables in the BuildCriteria expression with numeric values, it
works correctly. I think I am missing something obvious, and I would be
grateful if someone can tell me what it is.

Leonard Priestley

 

[Duane Hookom]

What is the value of strWhereClause prior the DoCmd.OpenReport?
Use either MsgBox... or debug.print to get the value.

--
Duane Hookom
MS Access MVP

I hope someone can help me spot the problem which has taken heaps of time,
and is causing much despair.

I am creating a database to track quality assurance testing on equipment
on
two sites. Six months, beginning in April, is spent on one site, then
testing commences on the second site in October. I want to produce
reports
covering work done each month, and also cumulative reports which cover
from
the beginning of the test period to the current month, inclusive. In this
case, specifying the site of interest automatically enables me to also
specify the first month of the period for a cumulative report.

In the past I have used the BuildCriteria Method to pick up values from
controls on a main report sheet and produce a Where clause, to be used in
a
line of the form:
DoCmd.OpenReport strDocName, acPreview, , strWhereClause

A report for a particular month works fine. The code to produce the
criteria looks like this (I have left some bits out for simplicity):

strWhereClause = "(" & BuildCriteria("SiteID", dbLong,
Form_frmReportMain.cboSite) & ")"
'Gives the site
strWhereClause = strWhereClause & " AND "
'concatenated with
strWhereClause = strWhereClause & "(" & BuildCriteria("MonthNotFound",
dbLong, Form_frmReportMain.cboMonth) & ")" 'the month of interest

( Interestingly, this works OK if I specify the datatype dbInteger instead
of dbLong)

My problem has arisen when I try to construct criteria for a cumulative
report:

I begin by specifying the site again, and then I use a few lines of code
to
get the number 4 or 10 (that is, the months April or October) and place
this
in a
textbox (txtBegin) on frmReportMain, to be picked up by the BuildCriteria
code. The code I use to build a criterion for the period between the
beginning of the test period and the month of interest is:

Dim intStartMonth As Integer
Dim intThisMonth As Integer

intStartMonth = Form_frmReportMain.[txtBegin]
intThisMonth = Form_frmReportMain.[cboMonth]

strWhereClause = strWhereClause & "(" & BuildCriteria("MonthNotFound",
dbInteger, "Between intStartMonth and intThisMonth") & ")"

This produces a WhereClause which the DoCmd.OpenReport code will not
accept.
I keep getting a message "Datatype mismatch in criteria expression"

I have tried redefining variables as integers or long integers. I have
tried changing the datatypes in the BuildCriteria expression . I have
checked what happens at various point in the program, and I can't seem to
sort this out.
If I use a messagebox to show what happens after settig the values of
intStartMonth and intThisMonth, I get the correct values. If I replace
these variables in the BuildCriteria expression with numeric values, it
works correctly. I think I am missing something obvious, and I would be
grateful if someone can tell me what it is.

Leonard Priestley

 

[John Vinson]

strWhereClause = strWhereClause & "(" & BuildCriteria("MonthNotFound",
dbInteger, "Between intStartMonth and intThisMonth") & ")"

This produces a WhereClause which the DoCmd.OpenReport code will not accept.
I keep getting a message "Datatype mismatch in criteria expression"

It's looking for the text strings intStartMonth and intThisMonth.

Concatenate the values of these variables instead of the names:

strWhereClause = strWhereClause & "(" & _
BuildCriteria("MonthNotFound", dbInteger, _
"Between " & intStartMonth & " and " & intThisMonth & ") & ")"

John W. Vinson[MVP]

 

[Leonard Priestley]

John Vinson:

Thank you very much, you were right. I think I had been looking at the wood
for so long I couldn't see the trees. At last I can get on with the other
fifteen or so reports.

Leonard Priestley

 

[Leonard Priestley]

Duane Hookum,

Thank you for your reply. I had another reply which solved the problem
immediately, so I hope you'll forgive me for not following your train of
thought further. I appreciate you taking the time to reply to my query.

Leonard Priestley

What is the value of strWhereClause prior the DoCmd.OpenReport?
Use either MsgBox... or debug.print to get the value.

--
Duane Hookom
MS Access MVP

I hope someone can help me spot the problem which has taken heaps of time,
and is causing much despair.

I am creating a database to track quality assurance testing on equipment
on
two sites. Six months, beginning in April, is spent on one site, then
testing commences on the second site in October. I want to produce
reports
covering work done each month, and also cumulative reports which cover
from
the beginning of the test period to the current month, inclusive. In this
case, specifying the site of interest automatically enables me to also
specify the first month of the period for a cumulative report.

In the past I have used the BuildCriteria Method to pick up values from
controls on a main report sheet and produce a Where clause, to be used in
a
line of the form:
DoCmd.OpenReport strDocName, acPreview, , strWhereClause

A report for a particular month works fine. The code to produce the
criteria looks like this (I have left some bits out for simplicity):

strWhereClause = "(" & BuildCriteria("SiteID", dbLong,
Form_frmReportMain.cboSite) & ")"
'Gives the site
strWhereClause = strWhereClause & " AND "
'concatenated with
strWhereClause = strWhereClause & "(" & BuildCriteria("MonthNotFound",
dbLong, Form_frmReportMain.cboMonth) & ")" 'the month of interest

( Interestingly, this works OK if I specify the datatype dbInteger instead
of dbLong)

My problem has arisen when I try to construct criteria for a cumulative
report:

I begin by specifying the site again, and then I use a few lines of code
to
get the number 4 or 10 (that is, the months April or October) and place
this
in a
textbox (txtBegin) on frmReportMain, to be picked up by the BuildCriteria
code. The code I use to build a criterion for the period between the
beginning of the test period and the month of interest is:

Dim intStartMonth As Integer
Dim intThisMonth As Integer

intStartMonth = Form_frmReportMain.[txtBegin]
intThisMonth = Form_frmReportMain.[cboMonth]

strWhereClause = strWhereClause & "(" & BuildCriteria("MonthNotFound",
dbInteger, "Between intStartMonth and intThisMonth") & ")"

This produces a WhereClause which the DoCmd.OpenReport code will not
accept.
I keep getting a message "Datatype mismatch in criteria expression"

I have tried redefining variables as integers or long integers. I have
tried changing the datatypes in the BuildCriteria expression . I have
checked what happens at various point in the program, and I can't seem to
sort this out.
If I use a messagebox to show what happens after settig the values of
intStartMonth and intThisMonth, I get the correct values. If I replace
these variables in the BuildCriteria expression with numeric values, it
works correctly. I think I am missing something obvious, and I would be
grateful if someone can tell me what it is.

Leonard Priestley