Parameter Query

[Guest]

I am using an unbound form to enter parameter criteria for my query. The
command box draws records from a two column X three row table. Column 1 is
the PKey..autonumber

Column two holds an expression in each row to pull certain date criteria.
Individually the expressions work correctly when individually placed in the
query criteria. Using the "build" function in the correct criteria I have
pointed to my command box on the unbound form. In the properties of the
command box the "bound column" is "2".

Anyone have any ideas on whats wrong or maybe expressions cannot be used as
parameters?? Thank You.

 

[Wayne Morgan]

Please go to SQL view of the query and copy/paste it into a message so we
can see what you have.

 

[Ken Snell [MVP]]

Please post more details (command box is a combo box?) about the RowSource
that you're using for the combo box, and what the query is that you're
using.

I am not quite understanding your setup yet.

 

[Guest]

Here's the SQL:

SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmTestRptMnu]![cboReports]));

 

[Ken Snell [MVP]]

Values in the columns of a combobox's row source record often are text
strings, regardless of what the data type is in the field from which they
come. Thus, ACCESS is not seeing a date/time value from your form, but
rather a date text string. So you'll need to convert the combo box value
from a string to a date:

SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=Format(CDate([Forms]![frmTestRptMnu]![cboReports]),"mm/dd/yyyy")));

--

Ken Snell
<MS ACCESS MVP>

Here's the SQL:

SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmTestRptMnu]![cboReports]));

I am using an unbound form to enter parameter criteria for my query. The
command box draws records from a two column X three row table. Column 1
is
the PKey..autonumber

Column two holds an expression in each row to pull certain date criteria.
Individually the expressions work correctly when individually placed in
the
query criteria. Using the "build" function in the correct criteria I
have
pointed to my command box on the unbound form. In the properties of the
command box the "bound column" is "2".

Anyone have any ideas on whats wrong or maybe expressions cannot be used
as
parameters?? Thank You.

 

[Guest]

Thanks Ken, I would have never found that on my own. I went to SQL of my
query and made changes matching your suggestion. Now when I make the
selection from my combo box I get an error:

Run Time 3071
This expression is typed incorrectly, or is too complex.....

this gives me the debugger option which sends me to my command button "OK"
which has the following code:

Private Sub cmdOK_Click()
'DoCmd.OpenQuery "qryTest", acViewNormal, acEdit
DoCmd.OpenReport "1_brptQueryTest", acViewPreview, acEdit
DoCmd.Close acForm, "frmTestRptMnu"
End Sub

The following is one of the expressions I am using which works fine when in
the criteria: on it's own.

(Between DateSerial(Year(Date()),Month(Date())-6,1) And
DateSerial(Year(Date()),Month(Date())-5,0)) OR (Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())-10,0))

Thanks for the help!

Values in the columns of a combobox's row source record often are text
strings, regardless of what the data type is in the field from which they
come. Thus, ACCESS is not seeing a date/time value from your form, but
rather a date text string. So you'll need to convert the combo box value
from a string to a date:

SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=Format(CDate([Forms]![frmTestRptMnu]![cboReports]),"mm/dd/yyyy")));

--

Ken Snell
<MS ACCESS MVP>

Here's the SQL:

SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmTestRptMnu]![cboReports]));

I am using an unbound form to enter parameter criteria for my query. The
command box draws records from a two column X three row table. Column 1
is
the PKey..autonumber

Column two holds an expression in each row to pull certain date criteria.
Individually the expressions work correctly when individually placed in
the
query criteria. Using the "build" function in the correct criteria I
have
pointed to my command box on the unbound form. In the properties of the
command box the "bound column" is "2".

Anyone have any ideas on whats wrong or maybe expressions cannot be used
as
parameters?? Thank You.

 

[Ken Snell [MVP]]

I am guessing that your form is closing before the query runs, and thus it's
not able to get the value from your combo box. Do you need to close the form
at that point? Perhaps you can just make it invisible and then use the
report's Close event procedure to close the form.
--

Ken Snell
<MS ACCESS MVP>

Thanks Ken, I would have never found that on my own. I went to SQL of my
query and made changes matching your suggestion. Now when I make the
selection from my combo box I get an error:

Run Time 3071
This expression is typed incorrectly, or is too complex.....

this gives me the debugger option which sends me to my command button "OK"
which has the following code:

Private Sub cmdOK_Click()
'DoCmd.OpenQuery "qryTest", acViewNormal, acEdit
DoCmd.OpenReport "1_brptQueryTest", acViewPreview, acEdit
DoCmd.Close acForm, "frmTestRptMnu"
End Sub

The following is one of the expressions I am using which works fine when
in
the criteria: on it's own.

(Between DateSerial(Year(Date()),Month(Date())-6,1) And
DateSerial(Year(Date()),Month(Date())-5,0)) OR (Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())-10,0))

Thanks for the help!

Values in the columns of a combobox's row source record often are text
strings, regardless of what the data type is in the field from which they
come. Thus, ACCESS is not seeing a date/time value from your form, but
rather a date text string. So you'll need to convert the combo box value
from a string to a date:

SELECT [All Employees].Last, [All Employees].First, [Child
Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=Format(CDate([Forms]![frmTestRptMnu]![cboReports]),"mm/dd/yyyy")));

--

Ken Snell
<MS ACCESS MVP>

Here's the SQL:

SELECT [All Employees].Last, [All Employees].First, [Child
Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON
[Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmTestRptMnu]![cboReports]));


:

I am using an unbound form to enter parameter criteria for my query.
The
command box draws records from a two column X three row table. Column
1
is
the PKey..autonumber

Column two holds an expression in each row to pull certain date
criteria.
Individually the expressions work correctly when individually placed
in
the
query criteria. Using the "build" function in the correct criteria I
have
pointed to my command box on the unbound form. In the properties of
the
command box the "bound column" is "2".

Anyone have any ideas on whats wrong or maybe expressions cannot be
used
as
parameters?? Thank You.

 

[Guest]

Thanks again Ken, I will play with that in the morning and post back here if
your listening then.

I am guessing that your form is closing before the query runs, and thus it's
not able to get the value from your combo box. Do you need to close the form
at that point? Perhaps you can just make it invisible and then use the
report's Close event procedure to close the form.
--

Ken Snell
<MS ACCESS MVP>

Thanks Ken, I would have never found that on my own. I went to SQL of my
query and made changes matching your suggestion. Now when I make the
selection from my combo box I get an error:

Run Time 3071
This expression is typed incorrectly, or is too complex.....

this gives me the debugger option which sends me to my command button "OK"
which has the following code:

Private Sub cmdOK_Click()
'DoCmd.OpenQuery "qryTest", acViewNormal, acEdit
DoCmd.OpenReport "1_brptQueryTest", acViewPreview, acEdit
DoCmd.Close acForm, "frmTestRptMnu"
End Sub

The following is one of the expressions I am using which works fine when
in
the criteria: on it's own.

(Between DateSerial(Year(Date()),Month(Date())-6,1) And
DateSerial(Year(Date()),Month(Date())-5,0)) OR (Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())-10,0))

Thanks for the help!

Values in the columns of a combobox's row source record often are text
strings, regardless of what the data type is in the field from which they
come. Thus, ACCESS is not seeing a date/time value from your form, but
rather a date text string. So you'll need to convert the combo box value
from a string to a date:

SELECT [All Employees].Last, [All Employees].First, [Child
Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=Format(CDate([Forms]![frmTestRptMnu]![cboReports]),"mm/dd/yyyy")));

--

Ken Snell
<MS ACCESS MVP>



Here's the SQL:

SELECT [All Employees].Last, [All Employees].First, [Child
Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON
[Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmTestRptMnu]![cboReports]));


:

I am using an unbound form to enter parameter criteria for my query.
The
command box draws records from a two column X three row table. Column
1
is
the PKey..autonumber

Column two holds an expression in each row to pull certain date
criteria.
Individually the expressions work correctly when individually placed
in
the
query criteria. Using the "build" function in the correct criteria I
have
pointed to my command box on the unbound form. In the properties of
the
command box the "bound column" is "2".

Anyone have any ideas on whats wrong or maybe expressions cannot be
used
as
parameters?? Thank You.

 

[Wayne Morgan]

I have seen this before. Try using your original query, but in query design
view go to Query|Parameters on the menu bar and enter the parameter exactly
as you have it typed (i.e. copy/paste). Set the data type for that parameter
to Date/Time.

Once you do this, the SQL will look like:

PARAMETERS [Forms]![frmTestRptMnu]![cboReports] DateTime;
SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmTestRptMnu]![cboReports]));

You could also make the change directly in the SQL. This will tell the query
what the data type of the parameter is. Date/Time seems to be the only one
that causes this problem, at least that I've run across so far.

--
Wayne Morgan
MS Access MVP

Thanks Ken, I would have never found that on my own. I went to SQL of my
query and made changes matching your suggestion. Now when I make the
selection from my combo box I get an error:

Run Time 3071
This expression is typed incorrectly, or is too complex.....

this gives me the debugger option which sends me to my command button "OK"
which has the following code:

Private Sub cmdOK_Click()
'DoCmd.OpenQuery "qryTest", acViewNormal, acEdit
DoCmd.OpenReport "1_brptQueryTest", acViewPreview, acEdit
DoCmd.Close acForm, "frmTestRptMnu"
End Sub

The following is one of the expressions I am using which works fine when
in
the criteria: on it's own.

(Between DateSerial(Year(Date()),Month(Date())-6,1) And
DateSerial(Year(Date()),Month(Date())-5,0)) OR (Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())-10,0))

Thanks for the help!

Values in the columns of a combobox's row source record often are text
strings, regardless of what the data type is in the field from which they
come. Thus, ACCESS is not seeing a date/time value from your form, but
rather a date text string. So you'll need to convert the combo box value
from a string to a date:

SELECT [All Employees].Last, [All Employees].First, [Child
Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=Format(CDate([Forms]![frmTestRptMnu]![cboReports]),"mm/dd/yyyy")));

--

Ken Snell
<MS ACCESS MVP>

Here's the SQL:

SELECT [All Employees].Last, [All Employees].First, [Child
Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON
[Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmTestRptMnu]![cboReports]));


:

I am using an unbound form to enter parameter criteria for my query.
The
command box draws records from a two column X three row table. Column
1
is
the PKey..autonumber

Column two holds an expression in each row to pull certain date
criteria.
Individually the expressions work correctly when individually placed
in
the
query criteria. Using the "build" function in the correct criteria I
have
pointed to my command box on the unbound form. In the properties of
the
command box the "bound column" is "2".

Anyone have any ideas on whats wrong or maybe expressions cannot be
used
as
parameters?? Thank You.

 

[Ken Snell [MVP]]

I have seen this before. Try using your original query, but in query design
view go to Query|Parameters on the menu bar and enter the parameter exactly
as you have it typed (i.e. copy/paste). Set the data type for that
parameter to Date/Time.

Once you do this, the SQL will look like:

PARAMETERS [Forms]![frmTestRptMnu]![cboReports] DateTime;
SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmTestRptMnu]![cboReports]));

Ahhhh....thanks, Wayne. I so rarely need to use the PARAMETERS option that I
don't always recall that it's there....

 

[Guest]

Thank You

I have seen this before. Try using your original query, but in query design
view go to Query|Parameters on the menu bar and enter the parameter exactly
as you have it typed (i.e. copy/paste). Set the data type for that
parameter to Date/Time.

Once you do this, the SQL will look like:

PARAMETERS [Forms]![frmTestRptMnu]![cboReports] DateTime;
SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmTestRptMnu]![cboReports]));

Ahhhh....thanks, Wayne. I so rarely need to use the PARAMETERS option that I
don't always recall that it's there....

 

[Guest]

You guys have great minds...I have been struggling all morning and unable get
on here because of corporate computer downtime. Glad I ran home to check
this thread while at lunch. I'll give it another try. Hopefully this will
solve the problem. Thank You.

I have seen this before. Try using your original query, but in query design
view go to Query|Parameters on the menu bar and enter the parameter exactly
as you have it typed (i.e. copy/paste). Set the data type for that parameter
to Date/Time.

Once you do this, the SQL will look like:

PARAMETERS [Forms]![frmTestRptMnu]![cboReports] DateTime;
SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmTestRptMnu]![cboReports]));

You could also make the change directly in the SQL. This will tell the query
what the data type of the parameter is. Date/Time seems to be the only one
that causes this problem, at least that I've run across so far.

--
Wayne Morgan
MS Access MVP

Thanks Ken, I would have never found that on my own. I went to SQL of my
query and made changes matching your suggestion. Now when I make the
selection from my combo box I get an error:

Run Time 3071
This expression is typed incorrectly, or is too complex.....

this gives me the debugger option which sends me to my command button "OK"
which has the following code:

Private Sub cmdOK_Click()
'DoCmd.OpenQuery "qryTest", acViewNormal, acEdit
DoCmd.OpenReport "1_brptQueryTest", acViewPreview, acEdit
DoCmd.Close acForm, "frmTestRptMnu"
End Sub

The following is one of the expressions I am using which works fine when
in
the criteria: on it's own.

(Between DateSerial(Year(Date()),Month(Date())-6,1) And
DateSerial(Year(Date()),Month(Date())-5,0)) OR (Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())-10,0))

Thanks for the help!

Values in the columns of a combobox's row source record often are text
strings, regardless of what the data type is in the field from which they
come. Thus, ACCESS is not seeing a date/time value from your form, but
rather a date text string. So you'll need to convert the combo box value
from a string to a date:

SELECT [All Employees].Last, [All Employees].First, [Child
Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=Format(CDate([Forms]![frmTestRptMnu]![cboReports]),"mm/dd/yyyy")));

--

Ken Snell
<MS ACCESS MVP>



Here's the SQL:

SELECT [All Employees].Last, [All Employees].First, [Child
Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON
[Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmTestRptMnu]![cboReports]));


:

I am using an unbound form to enter parameter criteria for my query.
The
command box draws records from a two column X three row table. Column
1
is
the PKey..autonumber

Column two holds an expression in each row to pull certain date
criteria.
Individually the expressions work correctly when individually placed
in
the
query criteria. Using the "build" function in the correct criteria I
have
pointed to my command box on the unbound form. In the properties of
the
command box the "bound column" is "2".

Anyone have any ideas on whats wrong or maybe expressions cannot be
used
as
parameters?? Thank You.

 

[Guest]

The following is the new SQL to my Query:

PARAMETERS [Forms]![frmReportMenu]![cboReports] DateTime;
SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM tblReports, (([All Employees] INNER JOIN [Child Query] ON [All
Employees].ID = [Child Query].ParentTable_ID) INNER JOIN
qryDirectorAssignments ON [Child Query].Dept = qryDirectorAssignments.[Acct
#]) INNER JOIN tblDirectorsList ON qryDirectorAssignments.Name =
tblDirectorsList.Name
WHERE (((tblDirectorsList.Dir_ID)=[Forms]![frmReportMenu]![cboDirectors])
AND (([Child Query].Active)=Yes) AND (([Child Query].[Current TB Test
Date])=[Forms]![frmReportMenu]![cboReports]));

One of my expressions (from a table that cboReports draws from) follows:

(Between DateSerial(Year(Date()),Month(Date())-6,1) And
DateSerial(Year(Date()),Month(Date())-5,0)) OR Month([Current TB Test Date])
& Year([Current TB Test Date])=Month(Date()) & Year(Date())-"1"

This and the other expressions in the table all run in the query returning
correct results when pasted into the Criteria: under (([Child Query].[Current
TB Test Date]).

The following is the code from the OK command button in my dialog box:

Option Compare Database

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "frmReportMenu"
End Sub

Private Sub cmdOK_Click()
DoCmd.OpenReport "1_arptquerytest", acViewPreview, acEdit
DoCmd.Close acForm, "frmReportMenu"
End Sub

I applied the “Parameter†suggestion and get no errors now…but I also get an
empty report. If I remove the parameter having to do with “cboReportsâ€
(which refers to the above expression) the query runs fine from the dialog
when choosing from “cboDirectorsâ€, and I get all records belonging to the
"Director_ID".

Any ideas ? I’ll appreciate any help. Thank You.

I have seen this before. Try using your original query, but in query design
view go to Query|Parameters on the menu bar and enter the parameter exactly
as you have it typed (i.e. copy/paste). Set the data type for that parameter
to Date/Time.

Once you do this, the SQL will look like:

PARAMETERS [Forms]![frmTestRptMnu]![cboReports] DateTime;
SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmTestRptMnu]![cboReports]));

You could also make the change directly in the SQL. This will tell the query
what the data type of the parameter is. Date/Time seems to be the only one
that causes this problem, at least that I've run across so far.

--
Wayne Morgan
MS Access MVP

Thanks Ken, I would have never found that on my own. I went to SQL of my
query and made changes matching your suggestion. Now when I make the
selection from my combo box I get an error:

Run Time 3071
This expression is typed incorrectly, or is too complex.....

this gives me the debugger option which sends me to my command button "OK"
which has the following code:

Private Sub cmdOK_Click()
'DoCmd.OpenQuery "qryTest", acViewNormal, acEdit
DoCmd.OpenReport "1_brptQueryTest", acViewPreview, acEdit
DoCmd.Close acForm, "frmTestRptMnu"
End Sub

The following is one of the expressions I am using which works fine when
in
the criteria: on it's own.

(Between DateSerial(Year(Date()),Month(Date())-6,1) And
DateSerial(Year(Date()),Month(Date())-5,0)) OR (Between
DateSerial(Year(Date()),Month(Date())-11,1) And
DateSerial(Year(Date()),Month(Date())-10,0))

Thanks for the help!

Values in the columns of a combobox's row source record often are text
strings, regardless of what the data type is in the field from which they
come. Thus, ACCESS is not seeing a date/time value from your form, but
rather a date text string. So you'll need to convert the combo box value
from a string to a date:

SELECT [All Employees].Last, [All Employees].First, [Child
Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=Format(CDate([Forms]![frmTestRptMnu]![cboReports]),"mm/dd/yyyy")));

--

Ken Snell
<MS ACCESS MVP>



Here's the SQL:

SELECT [All Employees].Last, [All Employees].First, [Child
Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON
[Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmTestRptMnu]![cboReports]));


:

I am using an unbound form to enter parameter criteria for my query.
The
command box draws records from a two column X three row table. Column
1
is
the PKey..autonumber

Column two holds an expression in each row to pull certain date
criteria.
Individually the expressions work correctly when individually placed
in
the
query criteria. Using the "build" function in the correct criteria I
have
pointed to my command box on the unbound form. In the properties of
the
command box the "bound column" is "2".

Anyone have any ideas on whats wrong or maybe expressions cannot be
used
as
parameters?? Thank You.

 

[Wayne Morgan]

(Between DateSerial(Year(Date()),Month(Date())-6,1) And

DateSerial(Year(Date()),Month(Date())-5,0)) OR Month([Current TB Test
Date])
& Year([Current TB Test Date])=Month(Date()) & Year(Date())-"1"

By chance should this be:
WHERE ...Other Filters (if any)... And
([Current TB Test Date] Between DateSerial(Year(Date()),Month(Date())-6,1)
And DateSerial(Year(Date()),Month(Date())-5,0)) OR Month([Current TB Test
Date]) & Year([Current TB Test Date])=Month(Date()) & Year(Date())-1

I assume you're wanting to subtract one year from the current date.

--
Wayne Morgan
MS Access MVP

The following is the new SQL to my Query:

PARAMETERS [Forms]![frmReportMenu]![cboReports] DateTime;
SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM tblReports, (([All Employees] INNER JOIN [Child Query] ON [All
Employees].ID = [Child Query].ParentTable_ID) INNER JOIN
qryDirectorAssignments ON [Child Query].Dept =
qryDirectorAssignments.[Acct
#]) INNER JOIN tblDirectorsList ON qryDirectorAssignments.Name =
tblDirectorsList.Name
WHERE (((tblDirectorsList.Dir_ID)=[Forms]![frmReportMenu]![cboDirectors])
AND (([Child Query].Active)=Yes) AND (([Child Query].[Current TB Test
Date])=[Forms]![frmReportMenu]![cboReports]));

One of my expressions (from a table that cboReports draws from) follows:

(Between DateSerial(Year(Date()),Month(Date())-6,1) And
DateSerial(Year(Date()),Month(Date())-5,0)) OR Month([Current TB Test
Date])
& Year([Current TB Test Date])=Month(Date()) & Year(Date())-"1"

This and the other expressions in the table all run in the query returning
correct results when pasted into the Criteria: under (([Child
Query].[Current
TB Test Date]).

The following is the code from the OK command button in my dialog box:

Option Compare Database

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "frmReportMenu"
End Sub

Private Sub cmdOK_Click()
DoCmd.OpenReport "1_arptquerytest", acViewPreview, acEdit
DoCmd.Close acForm, "frmReportMenu"
End Sub

I applied the "Parameter" suggestion and get no errors now.but I also get
an
empty report. If I remove the parameter having to do with "cboReports"
(which refers to the above expression) the query runs fine from the dialog
when choosing from "cboDirectors", and I get all records belonging to the
"Director_ID".

Any ideas ? I'll appreciate any help. Thank You.

 

[Guest]

I changed the expression to reflect your suggestion and stll get an empty
report, however if I remove the query and reference to the combo box, and
insert the new expression into the criteria: the query runs...still. I
finally got all the expressions to work in the Query but now they just won't
run from the combo box.

I have been struggling with dates for a number of weeks. The work my
database records is all based on the relationship of a date (recorded as
mm/dd/yyyy) in each record with “Today’s Dateâ€, whenever today is. In
November and December I built reports which all worked fine but they returned
nothing on January 2, 2004 (ie. Some functions do not recognize months less
than 1). You have seen one of my expressions, maybe I should just redo them
all so they work properly. What I use are as follows:

1. All dates in the month 6 months ago
2. All dates in the month 12 months ago
3. All dates older than 6 months ago this month
4. All dates older than 12 months ago this month

This way I can get one report showing every record due in the current month.

By chance should this be:

WHERE ...Other Filters (if any)... And
([Current TB Test Date] Between DateSerial(Year(Date()),Month(Date())-6,1)
And DateSerial(Year(Date()),Month(Date())-5,0)) OR Month([Current TB Test
Date]) & Year([Current TB Test Date])=Month(Date()) & Year(Date())-1

I assume you're wanting to subtract one year from the current date.

When the expression runs it pulls records with dates from July '04 and Jan.
'04
I also get a report which pulls Aug '04 and Feb. '04 but then next month
will pull Sept and march

Thanks again

--
Wayne Morgan
MS Access MVP

The following is the new SQL to my Query:

PARAMETERS [Forms]![frmReportMenu]![cboReports] DateTime;
SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM tblReports, (([All Employees] INNER JOIN [Child Query] ON [All
Employees].ID = [Child Query].ParentTable_ID) INNER JOIN
qryDirectorAssignments ON [Child Query].Dept =
qryDirectorAssignments.[Acct
#]) INNER JOIN tblDirectorsList ON qryDirectorAssignments.Name =
tblDirectorsList.Name
WHERE (((tblDirectorsList.Dir_ID)=[Forms]![frmReportMenu]![cboDirectors])
AND (([Child Query].Active)=Yes) AND (([Child Query].[Current TB Test
Date])=[Forms]![frmReportMenu]![cboReports]));

One of my expressions (from a table that cboReports draws from) follows:

(Between DateSerial(Year(Date()),Month(Date())-6,1) And
DateSerial(Year(Date()),Month(Date())-5,0)) OR Month([Current TB Test
Date])
& Year([Current TB Test Date])=Month(Date()) & Year(Date())-"1"

This and the other expressions in the table all run in the query returning
correct results when pasted into the Criteria: under (([Child
Query].[Current
TB Test Date]).

The following is the code from the OK command button in my dialog box:

Option Compare Database

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "frmReportMenu"
End Sub

Private Sub cmdOK_Click()
DoCmd.OpenReport "1_arptquerytest", acViewPreview, acEdit
DoCmd.Close acForm, "frmReportMenu"
End Sub

I applied the "Parameter" suggestion and get no errors now.but I also get
an
empty report. If I remove the parameter having to do with "cboReports"
(which refers to the above expression) the query runs fine from the dialog
when choosing from "cboDirectors", and I get all records belonging to the
"Director_ID".

Any ideas ? I'll appreciate any help. Thank You.

 

[Ken Snell [MVP]]

Just a thought, but what is the format of the combo box control (design view
of the form)? If it's not ShortDate, perhaps the PARAMETERS clause isn't
making the correct conversion? Set it to ShortDate and try it.

--

Ken Snell
<MS ACCESS MVP>

I changed the expression to reflect your suggestion and stll get an empty
report, however if I remove the query and reference to the combo box, and
insert the new expression into the criteria: the query runs...still. I
finally got all the expressions to work in the Query but now they just
won't
run from the combo box.

I have been struggling with dates for a number of weeks. The work my
database records is all based on the relationship of a date (recorded as
mm/dd/yyyy) in each record with "Today's Date", whenever today is. In
November and December I built reports which all worked fine but they
returned
nothing on January 2, 2004 (ie. Some functions do not recognize months
less
than 1). You have seen one of my expressions, maybe I should just redo
them
all so they work properly. What I use are as follows:

1. All dates in the month 6 months ago
2. All dates in the month 12 months ago
3. All dates older than 6 months ago this month
4. All dates older than 12 months ago this month

This way I can get one report showing every record due in the current
month.

By chance should this be:

WHERE ...Other Filters (if any)... And
([Current TB Test Date] Between
DateSerial(Year(Date()),Month(Date())-6,1)
And DateSerial(Year(Date()),Month(Date())-5,0)) OR Month([Current TB Test
Date]) & Year([Current TB Test Date])=Month(Date()) & Year(Date())-1

I assume you're wanting to subtract one year from the current date.

When the expression runs it pulls records with dates from July '04 and
Jan.
'04
I also get a report which pulls Aug '04 and Feb. '04 but then next month
will pull Sept and march

Thanks again

--
Wayne Morgan
MS Access MVP

The following is the new SQL to my Query:

PARAMETERS [Forms]![frmReportMenu]![cboReports] DateTime;
SELECT [All Employees].Last, [All Employees].First, [Child
Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM tblReports, (([All Employees] INNER JOIN [Child Query] ON [All
Employees].ID = [Child Query].ParentTable_ID) INNER JOIN
qryDirectorAssignments ON [Child Query].Dept =
qryDirectorAssignments.[Acct
#]) INNER JOIN tblDirectorsList ON qryDirectorAssignments.Name =
tblDirectorsList.Name
WHERE
(((tblDirectorsList.Dir_ID)=[Forms]![frmReportMenu]![cboDirectors])
AND (([Child Query].Active)=Yes) AND (([Child Query].[Current TB Test
Date])=[Forms]![frmReportMenu]![cboReports]));

One of my expressions (from a table that cboReports draws from)
follows:

(Between DateSerial(Year(Date()),Month(Date())-6,1) And
DateSerial(Year(Date()),Month(Date())-5,0)) OR Month([Current TB Test
Date])
& Year([Current TB Test Date])=Month(Date()) & Year(Date())-"1"

This and the other expressions in the table all run in the query
returning
correct results when pasted into the Criteria: under (([Child
Query].[Current
TB Test Date]).

The following is the code from the OK command button in my dialog box:

Option Compare Database

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "frmReportMenu"
End Sub

Private Sub cmdOK_Click()
DoCmd.OpenReport "1_arptquerytest", acViewPreview, acEdit
DoCmd.Close acForm, "frmReportMenu"
End Sub

I applied the "Parameter" suggestion and get no errors now.but I also
get
an
empty report. If I remove the parameter having to do with "cboReports"
(which refers to the above expression) the query runs fine from the
dialog
when choosing from "cboDirectors", and I get all records belonging to
the
"Director_ID".

Any ideas ? I'll appreciate any help. Thank You.

 

[Wayne Morgan]

You say that you add these expressions from a combo box. Are you adjusting
the .SQL property of the query? If so, then the query changes each time.
Please post a copy of the SQL that doesn't work.

If this isn't how you're "running the expression from the combo box", what
exactly are you doing?

--
Wayne Morgan
MS Access MVP

I changed the expression to reflect your suggestion and stll get an empty
report, however if I remove the query and reference to the combo box, and
insert the new expression into the criteria: the query runs...still. I
finally got all the expressions to work in the Query but now they just
won't
run from the combo box.

I have been struggling with dates for a number of weeks. The work my
database records is all based on the relationship of a date (recorded as
mm/dd/yyyy) in each record with "Today's Date", whenever today is. In
November and December I built reports which all worked fine but they
returned
nothing on January 2, 2004 (ie. Some functions do not recognize months
less
than 1). You have seen one of my expressions, maybe I should just redo
them
all so they work properly. What I use are as follows:

1. All dates in the month 6 months ago
2. All dates in the month 12 months ago
3. All dates older than 6 months ago this month
4. All dates older than 12 months ago this month

This way I can get one report showing every record due in the current
month.

By chance should this be:

WHERE ...Other Filters (if any)... And
([Current TB Test Date] Between
DateSerial(Year(Date()),Month(Date())-6,1)
And DateSerial(Year(Date()),Month(Date())-5,0)) OR Month([Current TB Test
Date]) & Year([Current TB Test Date])=Month(Date()) & Year(Date())-1

I assume you're wanting to subtract one year from the current date.

When the expression runs it pulls records with dates from July '04 and
Jan.
'04
I also get a report which pulls Aug '04 and Feb. '04 but then next month
will pull Sept and march

 

[Guest]

I followed a suggestion from a post made by an MVP which referenced Michael
Green's article "Customizing Access Parameter Queries" ...
http://www.fontstuff.com/access/index.htm.

My unbound form/dialog has a combo box for the director which points to a
query matching "many" depts....to each director. This functions fine.

The second combo box points to a single column table with three rows, each
row holding an expression as referenced earlier. These expressions, if
pasted into the Criteria: for [Current TB Test Date], individually result in
a correctly functioning query. When the Criteria: is changed through the
"build" function pointing to my frm...combo box...the query results in an
empty report. Hope this is enough info, details are below. I am baffled.

I also can't express enough appreciation, each struggle with this is a great
learning experience. Thanks

Unbound form: frmReportMenu: Combo Box Properties

Name: cboReports
Control Source: empty
Format: empty
Row Source Type: Table/Query
Row Source: SELECT [tblReports].[Exp] FROM [tblReports];
Column Count: 1
Column Head: No
Bound Column: 1

OK Button

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "frmReportMenu"
End Sub

Private Sub cmdOK_Click()
DoCmd.OpenReport "1_arptquerytest", acViewPreview, acEdit
DoCmd.Close acForm, "frmReportMenu"
End Sub

tblReports: Single column table

Field Name: Exp
Data Type: Text
Field Size: 255
Default Display Control was Text Box, I also tried Combo Box which didn't
work so it is now Text Box

1_arptQueryTest
Record Source: qryBiannualTests

qryBiannualTests SQL

PARAMETERS [Forms]![frmReportMenu]![cboReports] DateTime;
SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM tblReports, (([All Employees] INNER JOIN [Child Query] ON [All
Employees].ID = [Child Query].ParentTable_ID) INNER JOIN
qryDirectorAssignments ON [Child Query].Dept = qryDirectorAssignments.[Acct
#]) INNER JOIN tblDirectorsList ON qryDirectorAssignments.Name =
tblDirectorsList.Name
WHERE ((([Child Query].[Current TB Test
Date])=[Forms]![frmReportMenu]![cboReports]) AND
((tblDirectorsList.Dir_ID)=[Forms]![frmReportMenu]![cboDirectors]) AND
(([Child Query].Active)=Yes));

 

[Guest]

Tried this this morning and it wouldn't accept the format...data type of the
expression in the table is TEXT so I am guessing that's the reason.

Just a thought, but what is the format of the combo box control (design view
of the form)? If it's not ShortDate, perhaps the PARAMETERS clause isn't
making the correct conversion? Set it to ShortDate and try it.

--

Ken Snell
<MS ACCESS MVP>

I changed the expression to reflect your suggestion and stll get an empty
report, however if I remove the query and reference to the combo box, and
insert the new expression into the criteria: the query runs...still. I
finally got all the expressions to work in the Query but now they just
won't
run from the combo box.

I have been struggling with dates for a number of weeks. The work my
database records is all based on the relationship of a date (recorded as
mm/dd/yyyy) in each record with "Today's Date", whenever today is. In
November and December I built reports which all worked fine but they
returned
nothing on January 2, 2004 (ie. Some functions do not recognize months
less
than 1). You have seen one of my expressions, maybe I should just redo
them
all so they work properly. What I use are as follows:

1. All dates in the month 6 months ago
2. All dates in the month 12 months ago
3. All dates older than 6 months ago this month
4. All dates older than 12 months ago this month

This way I can get one report showing every record due in the current
month.

By chance should this be:

WHERE ...Other Filters (if any)... And
([Current TB Test Date] Between
DateSerial(Year(Date()),Month(Date())-6,1)
And DateSerial(Year(Date()),Month(Date())-5,0)) OR Month([Current TB Test
Date]) & Year([Current TB Test Date])=Month(Date()) & Year(Date())-1

I assume you're wanting to subtract one year from the current date.

When the expression runs it pulls records with dates from July '04 and
Jan.
'04
I also get a report which pulls Aug '04 and Feb. '04 but then next month
will pull Sept and march

Thanks again

--
Wayne Morgan
MS Access MVP


The following is the new SQL to my Query:

PARAMETERS [Forms]![frmReportMenu]![cboReports] DateTime;
SELECT [All Employees].Last, [All Employees].First, [Child
Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM tblReports, (([All Employees] INNER JOIN [Child Query] ON [All
Employees].ID = [Child Query].ParentTable_ID) INNER JOIN
qryDirectorAssignments ON [Child Query].Dept =
qryDirectorAssignments.[Acct
#]) INNER JOIN tblDirectorsList ON qryDirectorAssignments.Name =
tblDirectorsList.Name
WHERE
(((tblDirectorsList.Dir_ID)=[Forms]![frmReportMenu]![cboDirectors])
AND (([Child Query].Active)=Yes) AND (([Child Query].[Current TB Test
Date])=[Forms]![frmReportMenu]![cboReports]));

One of my expressions (from a table that cboReports draws from)
follows:

(Between DateSerial(Year(Date()),Month(Date())-6,1) And
DateSerial(Year(Date()),Month(Date())-5,0)) OR Month([Current TB Test
Date])
& Year([Current TB Test Date])=Month(Date()) & Year(Date())-"1"

This and the other expressions in the table all run in the query
returning
correct results when pasted into the Criteria: under (([Child
Query].[Current
TB Test Date]).

The following is the code from the OK command button in my dialog box:

Option Compare Database

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "frmReportMenu"
End Sub

Private Sub cmdOK_Click()
DoCmd.OpenReport "1_arptquerytest", acViewPreview, acEdit
DoCmd.Close acForm, "frmReportMenu"
End Sub

I applied the "Parameter" suggestion and get no errors now.but I also
get
an
empty report. If I remove the parameter having to do with "cboReports"
(which refers to the above expression) the query runs fine from the
dialog
when choosing from "cboDirectors", and I get all records belonging to
the
"Director_ID".

Any ideas ? I'll appreciate any help. Thank You.

 

[Guest]

Looking further for the source of the problem.....the [Current TB Test Date]
is arrived at in a Query as follows:

DMax("[ReadOn]","Child","[ParentTable_ID] =" & [ID]) AS [Current TB Test Date]

Could this be the problem?