Passing Parameter From Form mismatched criteria

[Jeff C]

I need help understanding the difference between how to declare either a TEXT
or Integer as a parameter.

"[Forms]![FRpts].[Unit]" based on QFAC is used as criteria in Q_ExpRpt.

The function below works when using "[Forms]![FRpts].[fName]" where fName is
text and I enclose the criteria in quotes.

I cannot get it to work where "Unit" is used - Unit is an Integer.
Option Compare Database
Option Explicit

Public Function ExpRpt()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstFAC As DAO.Recordset
Dim strSQL As String, strTemp As String, strQReportSQL As String
Dim strfac As String
Dim strBookName As String

Const strQName As String = "zExpRptQry"
Const strQReportParameterName As String = "[Forms]![FRpts].[Unit]"

DoCmd.SetWarnings False

Set dbs = CurrentDb

strQReportSQL = dbs.QueryDefs("Q_ExpRpt").SQL
Set qdf = dbs.CreateQueryDef(strQName, strQReportSQL)
qdf.Close

strTemp = qdf.Name
Set qdf = Nothing

Set rstFAC = dbs.OpenRecordset("QFAC", dbOpenDynaset, dbReadOnly)
If rstFAC.EOF = False And rstFAC.BOF = False Then
rstFAC.MoveFirst
Do While rstFAC.EOF = False
strfac = CStr(rstFAC![ID])
strBookName = CStr(rstFAC![FacilityNameSubUnit])
strSQL = Replace(strQReportSQL, strQReportParameterName,
strfac, 1, -1, vbTextCompare)

Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strfac
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\ExpReports\" & strBookName & ".xls"

rstFAC.MoveNext
Loop
End If

rstFAC.Close
Set rstFAC = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

DoCmd.SetWarnings True

End Function


Any assistance appreciated - Thanks

 

[Jim Burke in Novi]

It sounds like you're saying that the parameter sometimes comes from the Unit
control and sometimes from the fName control on the FRpts form. If that is
the case then I would define a String variable, let's say reportParm, and
assign the appropriate value to that. I don't know how you decide whether to
use Unit or fName (if I have that right). If there's some sort of logiuc that
decides which to use then you'd have something like

If condition1 then
reportParm = Forms!FRpts!fName
else
reportParm = Forms!FRpts!Unit
End If

Then you would have

strSQL = Replace(strQReportSQL, reportParm,
strfac, 1, -1, vbTextCompare)

Also there's no need for constant you defined for the field name. Hope I'm

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

It sounds like you're saying that the parameter sometimes comes from the Unit
control and sometimes from the fName control on the FRpts form. If that is
the case then I would define a String variable, let's say reportParm, and
assign the appropriate value to that. I don't know how you decide whether to
use Unit or fName (if I have that right). If there's some sort of logiuc that
decides which to use then you'd have something like

If condition1 then
reportParm = Forms!FRpts!fName
else
reportParm = Forms!FRpts!Unit
End If

Then you would have

strSQL = Replace(strQReportSQL, reportParm,
strfac, 1, -1, vbTextCompare)

Also there's no need for constant you defined for the field name. Hope I'm
on the right track with what you're trying to do.

I should have been more specific - I have used the function for several
different applications and each time I have to modify it to meet the specific
reporting requirements. In this case I first got it working on the data set
with the fname variable which is a text field in QFAC.

I now want to use the UNIT field in the same query which is an integer and
it is in this case that I cannot get the parameter to pass. I believe the
problem is in the:

Const strQReportParameterName As String = "[Forms]![FRpts].[Unit]"

I don't know if this is the correct way to pass the parameter "UNIT" when
"UNIT" is an integer.

Also, there may be a better more correct method of doing this in the
function, (you know, always a student)

Thanks

I need help understanding the difference between how to declare either a TEXT
or Integer as a parameter.

"[Forms]![FRpts].[Unit]" based on QFAC is used as criteria in Q_ExpRpt.

The function below works when using "[Forms]![FRpts].[fName]" where fName is
text and I enclose the criteria in quotes.

I cannot get it to work where "Unit" is used - Unit is an Integer.
Option Compare Database
Option Explicit

Public Function ExpRpt()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstFAC As DAO.Recordset
Dim strSQL As String, strTemp As String, strQReportSQL As String
Dim strfac As String
Dim strBookName As String

Const strQName As String = "zExpRptQry"
Const strQReportParameterName As String = "[Forms]![FRpts].[Unit]"

DoCmd.SetWarnings False

Set dbs = CurrentDb

strQReportSQL = dbs.QueryDefs("Q_ExpRpt").SQL
Set qdf = dbs.CreateQueryDef(strQName, strQReportSQL)
qdf.Close

strTemp = qdf.Name
Set qdf = Nothing

Set rstFAC = dbs.OpenRecordset("QFAC", dbOpenDynaset, dbReadOnly)
If rstFAC.EOF = False And rstFAC.BOF = False Then
rstFAC.MoveFirst
Do While rstFAC.EOF = False
strfac = CStr(rstFAC![ID])
strBookName = CStr(rstFAC![FacilityNameSubUnit])
strSQL = Replace(strQReportSQL, strQReportParameterName,
strfac, 1, -1, vbTextCompare)

Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strfac
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\ExpReports\" & strBookName & ".xls"

rstFAC.MoveNext
Loop
End If

rstFAC.Close
Set rstFAC = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

DoCmd.SetWarnings True

End Function


Any assistance appreciated - Thanks

 

[Jim Burke in Novi]

It looks to me like you're taking the SQL from a query and replacing the
value represented by 'strQReportParameterName' with the value in 'srfac'. Is
that right?

Are [Forms]![FRpts].[fName] and [Forms]![FRpts].[Unit] both text boxes from
that form? It appears that way based on what you'e describing Maybe this is
where I'm confused. If they're both form textboxes, you should use '!"
instead of '.' - '.' normally denotes a property, not a control.

If my assumptions are right, then all you need to do is, instead of using
the constant 'strQReportParameterName', simply put the form control value you
need to use in it's place. So if you're using [Forms]![FRpts].[Unit], then use

strSQL = Replace(strQReportSQL, [Forms]![FRpts]![Unit],
strfac, 1, -1, vbTextC

and if you're using [Forms]![FRpts].[fName] then use

strSQL = Replace(strQReportSQL, [Forms]![FRpts]![fName],
strfac, 1, -1, vbTextC

There is no need to define that constant. Just reference the form control
value that you want to pass. I'm pretty sure if you do it this way it
shouldn't matter if it's text ot integer.


Now let me ask you this -

--
Jeff C
Live Well .. Be Happy In All You Do

It sounds like you're saying that the parameter sometimes comes from the Unit
control and sometimes from the fName control on the FRpts form. If that is
the case then I would define a String variable, let's say reportParm, and
assign the appropriate value to that. I don't know how you decide whether to
use Unit or fName (if I have that right). If there's some sort of logiuc that
decides which to use then you'd have something like

If condition1 then
reportParm = Forms!FRpts!fName
else
reportParm = Forms!FRpts!Unit
End If

Then you would have

strSQL = Replace(strQReportSQL, reportParm,
strfac, 1, -1, vbTextCompare)

Also there's no need for constant you defined for the field name. Hope I'm
on the right track with what you're trying to do.

I should have been more specific - I have used the function for several
different applications and each time I have to modify it to meet the specific
reporting requirements. In this case I first got it working on the data set
with the fname variable which is a text field in QFAC.

I now want to use the UNIT field in the same query which is an integer and
it is in this case that I cannot get the parameter to pass. I believe the
problem is in the:

Const strQReportParameterName As String = "[Forms]![FRpts].[Unit]"

I don't know if this is the correct way to pass the parameter "UNIT" when
"UNIT" is an integer.

Also, there may be a better more correct method of doing this in the
function, (you know, always a student)

Thanks

I need help understanding the difference between how to declare either a TEXT
or Integer as a parameter.

"[Forms]![FRpts].[Unit]" based on QFAC is used as criteria in Q_ExpRpt.

The function below works when using "[Forms]![FRpts].[fName]" where fName is
text and I enclose the criteria in quotes.

I cannot get it to work where "Unit" is used - Unit is an Integer.
Option Compare Database
Option Explicit

Public Function ExpRpt()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstFAC As DAO.Recordset
Dim strSQL As String, strTemp As String, strQReportSQL As String
Dim strfac As String
Dim strBookName As String

Const strQName As String = "zExpRptQry"
Const strQReportParameterName As String = "[Forms]![FRpts].[Unit]"

DoCmd.SetWarnings False

Set dbs = CurrentDb

strQReportSQL = dbs.QueryDefs("Q_ExpRpt").SQL
Set qdf = dbs.CreateQueryDef(strQName, strQReportSQL)
qdf.Close

strTemp = qdf.Name
Set qdf = Nothing

Set rstFAC = dbs.OpenRecordset("QFAC", dbOpenDynaset, dbReadOnly)
If rstFAC.EOF = False And rstFAC.BOF = False Then
rstFAC.MoveFirst
Do While rstFAC.EOF = False
strfac = CStr(rstFAC![ID])
strBookName = CStr(rstFAC![FacilityNameSubUnit])
strSQL = Replace(strQReportSQL, strQReportParameterName,
strfac, 1, -1, vbTextCompare)

Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strfac
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
strTemp, "C:\ExpReports\" & strBookName & ".xls"

rstFAC.MoveNext
Loop
End If

rstFAC.Close
Set rstFAC = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

DoCmd.SetWarnings True

End Function


Any assistance appreciated - Thanks

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

It looks to me like you're taking the SQL from a query and replacing the
value represented by 'strQReportParameterName' with the value in 'srfac'. Is
that right?

Are [Forms]![FRpts].[fName] and [Forms]![FRpts].[Unit] both text boxes from
that form? It appears that way based on what you'e describing Maybe this is
where I'm confused. If they're both form textboxes, you should use '!"
instead of '.' - '.' normally denotes a property, not a control.

If my assumptions are right, then all you need to do is, instead of using
the constant 'strQReportParameterName', simply put the form control value you
need to use in it's place. So if you're using [Forms]![FRpts].[Unit], then use

strSQL = Replace(strQReportSQL, [Forms]![FRpts]![Unit],
strfac, 1, -1, vbTextC

and if you're using [Forms]![FRpts].[fName] then use

strSQL = Replace(strQReportSQL, [Forms]![FRpts]![fName],
strfac, 1, -1, vbTextC

There is no need to define that constant. Just reference the form control
value that you want to pass. I'm pretty sure if you do it this way it
shouldn't matter if it's text ot integer.

When trying your suggestion I get an error "cannot find [forms]![Frpts] item
not in list.

When [Forms]![FRpts]![Unit] is used as the criteria in the query The
parameter box opens asking for the parameter rather than the parameter
feeding from the form. If "[Forms]![FRpts]![Unit]" is used as the criteria
in the query then I get a data type mismatch error.

I am puzzled.
snip

 

[tina]

sounds like the value of Unit is being coerced to a Text value in the query;
when a text value criteria is applied to a Number field, you get a data type
mismatch error - even if the value being passed is a numeric character. in
other words

"1" does not equal 1

please post the SQL statement stored in your string variable strQReportSQL -
*before* the code edits the SQL, so we can examine it.

hth

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

sounds like the value of Unit is being coerced to a Text value in the query;
when a text value criteria is applied to a Number field, you get a data type
mismatch error - even if the value being passed is a numeric character. in
other words

"1" does not equal 1

please post the SQL statement stored in your string variable strQReportSQL -
*before* the code edits the SQL, so we can examine it.

hth

Right Tina - The SQL below give me the "Enter Parameter .." message Box. I
have been playing around with the form trying different things so please
ignore any subtle differences from the initially posted code.

SQL Below:

SELECT dbo_dimFacility.FacilityUnitNumb, SurgeryData.[SubUnit#],
dbo_dimFacility.FacilityNameSubUnit, SurgeryData.pDate, Format([pDate],"mmm")
AS [Month], Format([pDate],"yyyy") AS [Year], SurgeryData.ACCT,
SurgeryData.[MR#], SurgeryData.[OR Service], SurgeryData.SERVICE,
SurgeryData.PHYSICIAN, ProviderData.SPECIALTY, SurgeryData.ProvNum,
IIf([GROUP] Is Null,"Solo Practitioner",[GROUP]) AS GRP

FROM (SurgeryData INNER JOIN dbo_dimFacility ON (SurgeryData.[SubUnit#] =
dbo_dimFacility.FacilitySubUnitNumb) AND (SurgeryData.[Unit#] =
dbo_dimFacility.FacilityUnitNumb)) LEFT JOIN ProviderData ON
(SurgeryData.ProvNum = ProviderData.[PHYS#]) AND (SurgeryData.[Unit#] =
ProviderData.[Unit#])

WHERE (((dbo_dimFacility.FacilityUnitNumb)=[Forms]![frm_FAC]![unit]) AND
((SurgeryData.IND)="p"));

Thanks for your help.

snip

 

[tina]

okay, well, this is pretty confusing. i asked you to post the value of
variable strQReportSQL *before* it is changed by the Replace function, and
you posted the below SQL statement. so far so good, hopefully.

but the Replace function says: look in the string of variable
strQReportSQL, find the string "[Forms]![FRpts].[Unit]", and replace it with
the string of variable strfac - which your posted code previously set to

strfac = CStr(rstFAC![ID]).

now, first of all, in looking at the SQL statement you posted, i can't find
string "[Forms]![FRpts].[Unit]". so probably the Replace function can't find
it either. suggest you add the following line of code immediately below the
Replace function call, as

Debug.Print strSQL

and break the code immediately below that line. run the code to the break,
then open the debug window, copy the text of strSQL, and post it back here,
so we can see what changes, if any, the Replace function made to the SQL
string.

second, if the Replace function IS working correctly, it is using a value
that you have coerced to a string. why are you turning the value of
rstFac!ID into a string, if it's supposed to be an Integer? at any rate,
let's see that modified SQL string, and go from there.

hth

--
Jeff C
Live Well .. Be Happy In All You Do

sounds like the value of Unit is being coerced to a Text value in the query;
when a text value criteria is applied to a Number field, you get a data type
mismatch error - even if the value being passed is a numeric character. in
other words

"1" does not equal 1

please post the SQL statement stored in your string variable strQReportSQL -
*before* the code edits the SQL, so we can examine it.

hth

Right Tina - The SQL below give me the "Enter Parameter .." message Box. I
have been playing around with the form trying different things so please
ignore any subtle differences from the initially posted code.

SQL Below:

SELECT dbo_dimFacility.FacilityUnitNumb, SurgeryData.[SubUnit#],
dbo_dimFacility.FacilityNameSubUnit, SurgeryData.pDate, Format([pDate],"mmm")
AS [Month], Format([pDate],"yyyy") AS [Year], SurgeryData.ACCT,
SurgeryData.[MR#], SurgeryData.[OR Service], SurgeryData.SERVICE,
SurgeryData.PHYSICIAN, ProviderData.SPECIALTY, SurgeryData.ProvNum,
IIf([GROUP] Is Null,"Solo Practitioner",[GROUP]) AS GRP

FROM (SurgeryData INNER JOIN dbo_dimFacility ON (SurgeryData.[SubUnit#] =
dbo_dimFacility.FacilitySubUnitNumb) AND (SurgeryData.[Unit#] =
dbo_dimFacility.FacilityUnitNumb)) LEFT JOIN ProviderData ON
(SurgeryData.ProvNum = ProviderData.[PHYS#]) AND (SurgeryData.[Unit#] =
ProviderData.[Unit#])

WHERE (((dbo_dimFacility.FacilityUnitNumb)=[Forms]![frm_FAC]![unit]) AND
((SurgeryData.IND)="p"));

Thanks for your help.

a
TEXT

snip

 

[Jim Burke in Novi]

Are you printing SQL text from a 'stored' named query, or is this SQL text
that you have defined in VBA? Maybe between the two of us we can get this
working! If this is SQL from your VBA code, I see part of the problem - you
have a form control value embedded in your SQL:

WHERE (((dbo_dimFacility.FacilityUnitNumb)=[Forms]![frm_FAC]![unit]) AND
((SurgeryData.IND)="p"));

assuming dbo_dimFacility.FacilityUnitNumb is a number, this should be
written as:

WHERE dbo_dimFacility.FacilityUnitNumb=" & [Forms]![frm_FAC]![unit] & " AND
SurgeryData.IND="p"

I got rid of the parentheses - in this case, cince you're only using AND and
no ORs they're not needed, and it gets hard to figrue out where they're all
supposed to be!

If, on the other hand, you are showing the SQL text from a 'stored' query,
it either means frm_FAC is not open when the query runs, or frm_FAC does not
have a control called unit. In either case, Access would not be able to
resolve the parameter and so would prompt you to enter the value.

Is 'unit' a text box on frm_FAC? That needs to be established! Or is it a
VBA variable or a value from a recordset or something along those lines? If
it is a textbox on frm_FAC, you have to make sure that form is still open
when the query is run.

--
Jeff C
Live Well .. Be Happy In All You Do

sounds like the value of Unit is being coerced to a Text value in the query;
when a text value criteria is applied to a Number field, you get a data type
mismatch error - even if the value being passed is a numeric character. in
other words

"1" does not equal 1

please post the SQL statement stored in your string variable strQReportSQL -
*before* the code edits the SQL, so we can examine it.

hth

Right Tina - The SQL below give me the "Enter Parameter .." message Box. I
have been playing around with the form trying different things so please
ignore any subtle differences from the initially posted code.

SQL Below:

SELECT dbo_dimFacility.FacilityUnitNumb, SurgeryData.[SubUnit#],
dbo_dimFacility.FacilityNameSubUnit, SurgeryData.pDate, Format([pDate],"mmm")
AS [Month], Format([pDate],"yyyy") AS [Year], SurgeryData.ACCT,
SurgeryData.[MR#], SurgeryData.[OR Service], SurgeryData.SERVICE,
SurgeryData.PHYSICIAN, ProviderData.SPECIALTY, SurgeryData.ProvNum,
IIf([GROUP] Is Null,"Solo Practitioner",[GROUP]) AS GRP

FROM (SurgeryData INNER JOIN dbo_dimFacility ON (SurgeryData.[SubUnit#] =
dbo_dimFacility.FacilitySubUnitNumb) AND (SurgeryData.[Unit#] =
dbo_dimFacility.FacilityUnitNumb)) LEFT JOIN ProviderData ON
(SurgeryData.ProvNum = ProviderData.[PHYS#]) AND (SurgeryData.[Unit#] =
ProviderData.[Unit#])

WHERE (((dbo_dimFacility.FacilityUnitNumb)=[Forms]![frm_FAC]![unit]) AND
((SurgeryData.IND)="p"));

Thanks for your help.

snip

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

okay, well, this is pretty confusing. i asked you to post the value of
variable strQReportSQL *before* it is changed by the Replace function, and
you posted the below SQL statement. so far so good, hopefully.

but the Replace function says: look in the string of variable
strQReportSQL, find the string "[Forms]![FRpts].[Unit]", and replace it with
the string of variable strfac - which your posted code previously set to

strfac = CStr(rstFAC![ID]).

now, first of all, in looking at the SQL statement you posted, i can't find
string "[Forms]![FRpts].[Unit]".

The string is in the Where statement

so probably the Replace function can't find

it either. suggest you add the following line of code immediately below the
Replace function call, as

Debug.Print strSQL

and break the code immediately below that line. run the code to the break,
then open the debug window, copy the text of strSQL, and post it back here,
so we can see what changes, if any, the Replace function made to the SQL
string.

second, if the Replace function IS working correctly, it is using a value
that you have coerced to a string. why are you turning the value of
rstFac!ID into a string, if it's supposed to be an Integer? at any rate,
let's see that modified SQL string, and go from there.

hth

Thank you Tina!! I was not recognizing the contradiction between the
declaration of the string parameter forms!frm_fac!unit and QFAC!ID. That was
the synchronization missing. All is working in this application now and I'll
know how to correctly modify in the future.

The end game in all this is using Access to store and generate unique data
sets exporting into Excel workbooks. Before the loop to the next "UNIT" I
insert code that formats pivot reports on the data.

Thanks again Tina

 

[tina]

you're welcome, glad you got it working.

--
Jeff C
Live Well .. Be Happy In All You Do

okay, well, this is pretty confusing. i asked you to post the value of
variable strQReportSQL *before* it is changed by the Replace function, and
you posted the below SQL statement. so far so good, hopefully.

but the Replace function says: look in the string of variable
strQReportSQL, find the string "[Forms]![FRpts].[Unit]", and replace it with
the string of variable strfac - which your posted code previously set to

strfac = CStr(rstFAC![ID]).

now, first of all, in looking at the SQL statement you posted, i can't find
string "[Forms]![FRpts].[Unit]".

The string is in the Where statement

so probably the Replace function can't find

it either. suggest you add the following line of code immediately below the
Replace function call, as

Debug.Print strSQL

and break the code immediately below that line. run the code to the break,
then open the debug window, copy the text of strSQL, and post it back here,
so we can see what changes, if any, the Replace function made to the SQL
string.

second, if the Replace function IS working correctly, it is using a value
that you have coerced to a string. why are you turning the value of
rstFac!ID into a string, if it's supposed to be an Integer? at any rate,
let's see that modified SQL string, and go from there.

hth

Thank you Tina!! I was not recognizing the contradiction between the
declaration of the string parameter forms!frm_fac!unit and QFAC!ID. That was
the synchronization missing. All is working in this application now and I'll
know how to correctly modify in the future.

The end game in all this is using Access to store and generate unique data
sets exporting into Excel workbooks. Before the loop to the next "UNIT" I
insert code that formats pivot reports on the data.

Thanks again Tina