Multiple queries with same filter problem.

[rebeca]

I have an access database that when a button is selected, it runs 4
queries then populates the information in a spreadsheet. The 4 queries
have the same parameter, yesterday's date. Currently the user needs to
enter the same date 4 times.

I've created an input form instead of launching the queries via macro
when the button is selected.. The input form asks the user to enter
the date. Then VB code takes over and launches the macro. However, the
date is not passing through to the queries. I have [forms]!
[SMMSQueryDate]![QueryDate1] listed in the condition box on all 4
queries. What have I done wrong? Is it because the VB code calls the
macro and not the individual queries?

Any help would be appreciated.

 

[KARL DEWEY]

The input form asks the user to enter the date.
How do you accomplish this? A form asking for an entry?

Does the form remain open?

 

[rebeca]

How do you accomplish this?  A form asking for an entry?

Does the form remain open?

--
Build a little, test a little.

I have an access database that when a button is selected, it runs 4
queries then populates the information in a spreadsheet. The 4 queries
have the same parameter, yesterday's date. Currently the user needs to
enter the same date 4 times.

I've created an input form instead of launching the queries via macro
when the button is selected.. The input form asks the user to enter
the date. Then VB code takes over and launches the macro. However, the
date is not passing through to the queries. I have [forms]!
[SMMSQueryDate]![QueryDate1] listed in the condition box on all 4
queries. What have I done wrong? Is it because the VB code calls the
macro and not the individual queries?

Any help would be appreciated.- Hide quoted text -

- Show quoted text -

Yes. The form opens when the button is selected. A date is input onto
the form, then a button labeled "Calculate Deposit" is selected. The
form does remain open.

 

[KARL DEWEY]

Test if the date is getting through by addin a calculated field in the query
like this --
MyParameter: [forms]![SMMSQueryDate]![QueryDate1]

After the button labeled "Calculate Deposit" is selected, leaving the form
open, go to the query and run it. See what you get for MyParameter.

--
Build a little, test a little.

The input form asks the user to enter the date.

How do you accomplish this? A form asking for an entry?

Does the form remain open?

--
Build a little, test a little.

I have an access database that when a button is selected, it runs 4
queries then populates the information in a spreadsheet. The 4 queries
have the same parameter, yesterday's date. Currently the user needs to
enter the same date 4 times.

I've created an input form instead of launching the queries via macro
when the button is selected.. The input form asks the user to enter
the date. Then VB code takes over and launches the macro. However, the
date is not passing through to the queries. I have [forms]!
[SMMSQueryDate]![QueryDate1] listed in the condition box on all 4
queries. What have I done wrong? Is it because the VB code calls the
macro and not the individual queries?

Any help would be appreciated.- Hide quoted text -

- Show quoted text -

Yes. The form opens when the button is selected. A date is input onto
the form, then a button labeled "Calculate Deposit" is selected. The
form does remain open.

 

[rebeca]

Test if the date is getting through by addin a calculated field in the query
like this --
  MyParameter:  [forms]![SMMSQueryDate]![QueryDate1]

After the button labeled "Calculate Deposit" is selected, leaving the form
open, go to the query and run it.  See what you get for MyParameter.

--
Build a little, test a little.

The input form asks the user to enter the date.
How do you accomplish this?  A form asking for an entry?
Does the form remain open?
--
Build a little, test a little.
:
I have an access database that when a button is selected, it runs 4
queries then populates the information in a spreadsheet. The 4 queries
have the same parameter, yesterday's date. Currently the user needsto
enter the same date 4 times.
I've created an input form instead of launching the queries via macro
when the button is selected.. The input form asks the user to enter
the date. Then VB code takes over and launches the macro. However, the
date is not passing through to the queries. I have [forms]!
[SMMSQueryDate]![QueryDate1] listed in the condition box on all 4
queries. What have I done wrong? Is it because the VB code calls the
macro and not the individual queries?
Any help would be appreciated.- Hide quoted text -
- Show quoted text -

Yes. The form opens when the button is selected. A date is input onto
the form, then a button labeled "Calculate Deposit" is selected. The
form does remain open.- Hide quoted text -

- Show quoted text -

The query returned no data at all, just the headers. I removed the
parameter from the date field, but left the MyParameter calculated
field to see if anything was coming through. MyParameter is blank.

 

[John Spencer]

If the date is always yesterday's date the change the criteria from
referencing the date on the form to the expression.

DateAdd("d",-1,Date())

If you need to use the form to set the date, then the question becomes
how are you calling the queries. Show us the code you are using.

If you are using
Docmd.OpenQuery "QueryName"
then the query should be able to use the reference to the form's control.


If you are using the execute method, the query cannot use the reference
to the form's control directly. Although there are ways to handle the
problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

I assume that after you removed the parameter from the date field you did get
records.
Is there a possibility of spelling error?

--
Build a little, test a little.

Test if the date is getting through by addin a calculated field in the query
like this --
MyParameter: [forms]![SMMSQueryDate]![QueryDate1]

After the button labeled "Calculate Deposit" is selected, leaving the form
open, go to the query and run it. See what you get for MyParameter.

--
Build a little, test a little.

On Aug 14, 12:31 pm, KARL DEWEY <[email protected]>
wrote:
The input form asks the user to enter the date.

How do you accomplish this? A form asking for an entry?

Does the form remain open?

:
I have an access database that when a button is selected, it runs 4
queries then populates the information in a spreadsheet. The 4 queries
have the same parameter, yesterday's date. Currently the user needs to
enter the same date 4 times.

I've created an input form instead of launching the queries via macro
when the button is selected.. The input form asks the user to enter
the date. Then VB code takes over and launches the macro. However, the
date is not passing through to the queries. I have [forms]!
[SMMSQueryDate]![QueryDate1] listed in the condition box on all 4
queries. What have I done wrong? Is it because the VB code calls the
macro and not the individual queries?

Any help would be appreciated.- Hide quoted text -

- Show quoted text -

Yes. The form opens when the button is selected. A date is input onto
the form, then a button labeled "Calculate Deposit" is selected. The
form does remain open.- Hide quoted text -

- Show quoted text -

The query returned no data at all, just the headers. I removed the
parameter from the date field, but left the MyParameter calculated
field to see if anything was coming through. MyParameter is blank.

 

[rebeca]

If the date is always yesterday's date the change the criteria from
referencing the date on the form to the expression.

   DateAdd("d",-1,Date())

If you need to use the form to set the date, then the question becomes
how are you calling the queries.  Show us the code you are using.

If you are using
   Docmd.OpenQuery "QueryName"
then the query should be able to use the reference to the form's control.

If you are using the execute method, the query cannot use the reference
to the form's control directly.  Although there are ways to handle the
problem.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

I have an access database that when a button is selected, it runs 4
queries then populates the information in a spreadsheet. The 4 queries
have the same parameter, yesterday's date. Currently the user needs to
enter the same date 4 times.

I've created an input form instead of launching the queries via macro
when the button is selected.. The input form asks the user to enter
the date. Then VB code takes over and launches the macro. However, the
date is not passing through to the queries. I have [forms]!
[SMMSQueryDate]![QueryDate1] listed in the condition box on all 4
queries. What have I done wrong? Is it because the VB code calls the
macro and not the individual queries?

Any help would be appreciated.- Hide quoted text -

- Show quoted text -

The date "should" always be yesterday (ideally previous business day's
date). But as we know there are always exceptions which is the reason
for the input box, those "just in case" scenarios The code I am
using to call the queries is below. I'm sure that it is just something
easy that I am missing.

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim oXL As Object
Dim oExcel As Object
Dim path As String
DoCmd.OpenQuery "Deposit Info - NG"
DoCmd.OpenQuery "Deposit Info - SN"
DoCmd.OpenQuery "Bad Debt Payments - NG"
DoCmd.OpenQuery "Bad Debt Payments - SN"
'Create a new Excel instance
Set oXL = CreateObject("Excel.Application")
'put the path to your file here
path = "V:\Skyview Medical Management Common\LLC Payment Log\smms
receptionist.xls"
'open the file
oXL.Visible = True
oXL.Workbooks.Open (path)
oXL.sheets("NGDepositInfo").Activate
oXL.Range("A6").Select
oXL.ActiveCell.QueryTable.Refresh
oXL.sheets("SNDepositInfo").Activate
oXL.Range("A6").Select
oXL.ActiveCell.QueryTable.Refresh
oXL.sheets("CollectionInfoNG").Activate
oXL.Range("A6").Select
oXL.ActiveCell.QueryTable.Refresh
oXL.sheets("CollectionInfoSN").Activate
oXL.Range("A6").Select
oXL.ActiveCell.QueryTable.Refresh
oXL.sheets("LLC Deposit").Activate

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

The queries themselves work fine on their own when the date is
manually inserted four times. I'm just trying to make it a little
easier for the user.

 

[rebeca]

I assume that after you removed the parameter from the date field you didget
records.
Is there a possibility of spelling error?

--
Build a little, test a little.

Test if the date is getting through by addin a calculated field in the query
like this --
  MyParameter:  [forms]![SMMSQueryDate]![QueryDate1]
After the button labeled "Calculate Deposit" is selected, leaving theform
open, go to the query and run it.  See what you get for MyParameter..
--
Build a little, test a little.
:
On Aug 14, 12:31 pm, KARL DEWEY <[email protected]>
wrote:
The input form asks the user to enter the date.
How do you accomplish this?  A form asking for an entry?
Does the form remain open?
--
Build a little, test a little.
:
I have an access database that when a button is selected, it runs 4
queries then populates the information in a spreadsheet. The 4 queries
have the same parameter, yesterday's date. Currently the user needs to
enter the same date 4 times.
I've created an input form instead of launching the queries viamacro
when the button is selected.. The input form asks the user to enter
the date. Then VB code takes over and launches the macro. However, the
date is not passing through to the queries. I have [forms]!
[SMMSQueryDate]![QueryDate1] listed in the condition box on all4
queries. What have I done wrong? Is it because the VB code calls the
macro and not the individual queries?
Any help would be appreciated.- Hide quoted text -
- Show quoted text -
Yes. The form opens when the button is selected. A date is input onto
the form, then a button labeled "Calculate Deposit" is selected. The
form does remain open.- Hide quoted text -
- Show quoted text -

The query returned no data at all, just the headers. I removed the
parameter from the date field, but left the MyParameter calculated
field to see if anything was coming through. MyParameter is blank.- Hide quoted text -

- Show quoted text -

I did get records, but I didn't get the same records that I would get
using [Enter Date]. I've checked and dbl-checked the parameters and am
not finding a spelling error.

 

[rebeca]

I assume that after you removed the parameter from the date field you did get
records.
Is there a possibility of spelling error?

On Aug 14, 12:56 pm, KARL DEWEY <[email protected]>
wrote:
Test if the date is getting through by addin a calculated field in the query
like this --
  MyParameter:  [forms]![SMMSQueryDate]![QueryDate1]
After the button labeled "Calculate Deposit" is selected, leaving the form
open, go to the query and run it.  See what you get for MyParameter.
--
Build a little, test a little.
:
On Aug 14, 12:31 pm, KARL DEWEY <[email protected]>
wrote:
The input form asks the user to enter the date.
How do you accomplish this?  A form asking for an entry?
Does the form remain open?
--
Build a little, test a little.
:
I have an access database that when a button is selected, it runs 4
queries then populates the information in a spreadsheet. The 4 queries
have the same parameter, yesterday's date. Currently the userneeds to
enter the same date 4 times.
I've created an input form instead of launching the queries via macro
when the button is selected.. The input form asks the user toenter
the date. Then VB code takes over and launches the macro. However, the
date is not passing through to the queries. I have [forms]!
[SMMSQueryDate]![QueryDate1] listed in the condition box on all 4
queries. What have I done wrong? Is it because the VB code calls the
macro and not the individual queries?
Any help would be appreciated.- Hide quoted text -
- Show quoted text -
Yes. The form opens when the button is selected. A date is input onto
the form, then a button labeled "Calculate Deposit" is selected. The
form does remain open.- Hide quoted text -
- Show quoted text -
The query returned no data at all, just the headers. I removed the
parameter from the date field, but left the MyParameter calculated
field to see if anything was coming through. MyParameter is blank.- Hide quoted text -

- Show quoted text -

I did get records, but I didn't get the same records that I would get
using [Enter Date]. I've checked and dbl-checked the parameters and am
not finding a spelling error.- Hide quoted text -

- Show quoted text -

Ok guys. I appreciate the help, but I think I figured it out. My day
is never complete unless I learn something I'll repost if I have
further problems.
Thanks again!

 

[John Spencer]

Post the SQL of one of your queries. I suspect that you need to declare
the parameter type in the query as it is not being interpreted correctly.

Parameters [FORMS]![NameOfForm]![NameOfControl] DateTime;




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

If the date is always yesterday's date the change the criteria from
referencing the date on the form to the expression.

DateAdd("d",-1,Date())

If you need to use the form to set the date, then the question becomes
how are you calling the queries. Show us the code you are using.

If you are using
Docmd.OpenQuery "QueryName"
then the query should be able to use the reference to the form's control.

If you are using the execute method, the query cannot use the reference
to the form's control directly. Although there are ways to handle the
problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have an access database that when a button is selected, it runs 4
queries then populates the information in a spreadsheet. The 4 queries
have the same parameter, yesterday's date. Currently the user needs to
enter the same date 4 times.
I've created an input form instead of launching the queries via macro
when the button is selected.. The input form asks the user to enter
the date. Then VB code takes over and launches the macro. However, the
date is not passing through to the queries. I have [forms]!
[SMMSQueryDate]![QueryDate1] listed in the condition box on all 4
queries. What have I done wrong? Is it because the VB code calls the
macro and not the individual queries?
Any help would be appreciated.- Hide quoted text -

- Show quoted text -

The date "should" always be yesterday (ideally previous business day's
date). But as we know there are always exceptions which is the reason
for the input box, those "just in case" scenarios The code I am
using to call the queries is below. I'm sure that it is just something
easy that I am missing.

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim oXL As Object
Dim oExcel As Object
Dim path As String
DoCmd.OpenQuery "Deposit Info - NG"
DoCmd.OpenQuery "Deposit Info - SN"
DoCmd.OpenQuery "Bad Debt Payments - NG"
DoCmd.OpenQuery "Bad Debt Payments - SN"
'Create a new Excel instance
Set oXL = CreateObject("Excel.Application")
'put the path to your file here
path = "V:\Skyview Medical Management Common\LLC Payment Log\smms
receptionist.xls"
'open the file
oXL.Visible = True
oXL.Workbooks.Open (path)
oXL.sheets("NGDepositInfo").Activate
oXL.Range("A6").Select
oXL.ActiveCell.QueryTable.Refresh
oXL.sheets("SNDepositInfo").Activate
oXL.Range("A6").Select
oXL.ActiveCell.QueryTable.Refresh
oXL.sheets("CollectionInfoNG").Activate
oXL.Range("A6").Select
oXL.ActiveCell.QueryTable.Refresh
oXL.sheets("CollectionInfoSN").Activate
oXL.Range("A6").Select
oXL.ActiveCell.QueryTable.Refresh
oXL.sheets("LLC Deposit").Activate

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

The queries themselves work fine on their own when the date is
manually inserted four times. I'm just trying to make it a little
easier for the user.

 

[rebeca]

Post the SQL of one of your queries.  I suspect that you need to declare
the parameter type in the query as it is not being interpreted correctly.

Parameters [FORMS]![NameOfForm]![NameOfControl] DateTime;

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

If the date is always yesterday's date the change the criteria from
referencing the date on the form to the expression.
   DateAdd("d",-1,Date())
If you need to use the form to set the date, then the question becomes
how are you calling the queries.  Show us the code you are using.
If you are using
   Docmd.OpenQuery "QueryName"
then the query should be able to use the reference to the form's control.
If you are using the execute method, the query cannot use the reference
to the form's control directly.  Although there are ways to handle the
problem.
'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================
rebeca wrote:
I have an access database that when a button is selected, it runs 4
queries then populates the information in a spreadsheet. The 4 queries
have the same parameter, yesterday's date. Currently the user needs to
enter the same date 4 times.
I've created an input form instead of launching the queries via macro
when the button is selected.. The input form asks the user to enter
the date. Then VB code takes over and launches the macro. However, the
date is not passing through to the queries. I have [forms]!
[SMMSQueryDate]![QueryDate1] listed in the condition box on all 4
queries. What have I done wrong? Is it because the VB code calls the
macro and not the individual queries?
Any help would be appreciated.- Hide quoted text -
- Show quoted text -

The date "should" always be yesterday (ideally previous business day's
date). But as we know there are always exceptions which is the reason
for the input box, those "just in case" scenarios The code I am
using to call the queries is below. I'm sure that it is just something
easy that I am missing.

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim stDocName As String
   Dim stLinkCriteria As String
   Dim oXL As Object
   Dim oExcel As Object
   Dim path As String
   DoCmd.OpenQuery "Deposit Info - NG"
   DoCmd.OpenQuery "Deposit Info - SN"
   DoCmd.OpenQuery "Bad Debt Payments - NG"
   DoCmd.OpenQuery "Bad Debt Payments - SN"
  'Create a new Excel instance
 Set oXL = CreateObject("Excel.Application")
 'put the path to your file here
  path = "V:\Skyview Medical Management Common\LLC Payment Log\smms
receptionist.xls"
 'open the file
  oXL.Visible = True
  oXL.Workbooks.Open (path)
  oXL.sheets("NGDepositInfo").Activate
  oXL.Range("A6").Select
  oXL.ActiveCell.QueryTable.Refresh
  oXL.sheets("SNDepositInfo").Activate
  oXL.Range("A6").Select
  oXL.ActiveCell.QueryTable.Refresh
  oXL.sheets("CollectionInfoNG").Activate
  oXL.Range("A6").Select
  oXL.ActiveCell.QueryTable.Refresh
  oXL.sheets("CollectionInfoSN").Activate
  oXL.Range("A6").Select
  oXL.ActiveCell.QueryTable.Refresh
  oXL.sheets("LLC Deposit").Activate

Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click

End Sub

The queries themselves work fine on their own when the date is
manually inserted four times. I'm just trying to make it a little
easier for the user.- Hide quoted text -

- Show quoted text -

Actually, it truly was a problem with my test database. Once I
corrected it, it worked fine. My code above was incomplete as well,
but all is good now. Thanks again for the assistance!