Help with VB and running report with query

  • Thread starter Thread starter VM
  • Start date Start date
V

VM

I'm new at VBA and I wanted to know how I'd be able to execute a report I
already created through a simple form with two textboxes and the exe cution
button.
Basically, the report asks for a "Before" and "After" date, and I'd like to
be able to create a form that:
1) Has two textboxes: BeforeDate and AfterDate
2) Has a button that, when pressed, runs a specific SQL statement based on
the Entered dates and sends the recordset to the report.

The form design is pretty easy. The only hard thing (for me) is that the
form interact with the report.

Thanks,
VM
 
Try the following code...Beware, there is no error handling
and it will fail if either date is empty.

Dim stDocName As String
Dim datBefore as Date, datAfter as Date

datStart = Me!BeforeDate
datEnd = Me!AfterDate

stDocName = "rptYourReportName"
DoCmd.OpenReport stDocName, acPreview, ,
"[DateToCompareFieldName] Between #" & datBefore & "# And #"
& datAfter & "#"

Gary Miller
Sisters, OR
 
Hi,

You run your report with docmd.OpenReport statement
It supports the WhereClause parameter, which can be any valid where clause
of a SQL statement (without WHERE keyword)

So suppose your form has the two date fields named BeforeDate and AfterDate,
your report is called "TestReport", your statement should be as follows:

Docmd.OpenReport "TestReport", , , "YourDateFld between #" &
me.BeforeDate.Value & "# and #" & me.AfterDateValue & "#"

where YourDateFld is a date type fied from one of the tables involved in the
recordsource query

# is used do delimitate date type values

HTH,
Bogdan
 
Thanks for your help.
But if I choose to use the whole query, how would I do it?

In my case, it'd be:
"Select * from TableX where BeforeDate >= " & me.BeforeDate.Value & " and
AfterDate <= " & me.AfterDate.Value

How would I incorporate this query into the VB Code?

Thanks again.
VM
 
How would I incorporate this query into the VB Code?

It all depends what you want to do with it. Open a recordset
maybe? You may need to give some more details of what you
are trying to do.

Do be aware that you are still missing the critical '#'
signs needed for date values.

Gary Miller
Sisters, OR
 
Yes.
The report, when you double-click on it, asks for the BeforeDate and
AfterDate (because the SQL query in the Access SQL view has
[Forms]![UnivCriteriaFrm]![txtFrom] and [Forms]![UnivCriteriaFrm]![txtTo]).
I'd like to be able to (literally) move this SQL statement into VBA. The
only difference would be that, instead of using
[Forms]![UnivCriteriaFrm]![txtFrom], I'll be referring to the VB textboxes
(me.BeforeDate.Value ).
Then, when I get the recordset from this query, I'd like to be able to
display it in the report.

What I'm not sure of is how to take that recordset and "move" it into the
report.

Vaughn
 
Hi,
The report, when you double-click on it, asks for the BeforeDate and
AfterDate (because the SQL query in the Access SQL view has
[Forms]![UnivCriteriaFrm]![txtFrom] and
Click to expand...
[Forms]![UnivCriteriaFrm]![txtTo]).

First, you should remove the condition ([Forms]![UnivCriteriaFrm]![txtFrom]
and [Forms]![UnivCriteriaFrm]![txtTo])) from the report's SQL recordsource.
Then you create a form with those two textboxes (beforeDate and AfterDate)
and two buttons, OK and Cancel

In cmdOK_Click add the following code

Private Sub cmdOK_Click() ' if user click this button,
'you set TAG property
(which is not used
' by Access, but left
for custom usage),
'and make form invisible
Me.Tag = "O"
Me.Visible = False
End Sub

In cmdCancel_Click add the following code

Private Sub CmdCancel_Click() ' just hide the form
Me.Tag = ""
Me.Visible = False
End Sub

Now when you exit the form, you know if user pressed OK you have the TAG set
to "O", otherwise it is empty

Now in the REPORT_Open method writebthe following code:

Private Sub Report_Open(Cancel As Integer)
Dim oFrm As Form
DoCmd.OpenForm "Form1", , , , , acDialog
Set oFrm = Forms!Form1
If oFrm.Tag <> "O" Then
MsgBox "You need to enter before and After dateto run this report"
Cancel = True
Else
Me.Filter = "[YourDateFld] between #" & oFrm.BeforeDate & "# and #"
& oFrm.AfterDate & "#"
Me.FilterOn = True
End If
End Sub

Now, when you click on report in database window, or try to start it from
code or in any other way (like adding it to a custom menu), the app will
open the date form. If user click OK, the report will open with the date
range user entered.

Few things you should take care of: in form 1, if user press OK, before
hiding the form you should validate data enterd by user, otherwise you might
get errors.

HTH,
Bogdan Zamfir
-------------------------------------
Freelance programmer
I'd like to be able to (literally) move this SQL statement into VBA. The
only difference would be that, instead of using
[Forms]![UnivCriteriaFrm]![txtFrom], I'll be referring to the VB textboxes
(me.BeforeDate.Value ).
Then, when I get the recordset from this query, I'd like to be able to
display it in the report.

What I'm not sure of is how to take that recordset and "move" it into the
report.

Vaughn

Gary Miller said:
It all depends what you want to do with it. Open a recordset
maybe? You may need to give some more details of what you
are trying to do.

Do be aware that you are still missing the critical '#'
signs needed for date values.

Gary Miller
Sisters, OR
Click to expand...
Click to expand...
 
Gotcha! Actually, you were very close at the beginning.
Bogdan gave you one method. Here is another that would be
good for opening it from a command button directly on your
form.

Dim strDocName As String, strSQL as String
Dim datBefore as Date, datAfter as Date

' Pick up the dates from your form. You will want some
' error handling in case they are empty

datBefore = Me.BeforeDate.Value
datAfter = Me.AfterDate.Value

strDocName = "rptYourReportName"

' Here is your SQL string
strSQL = "SELECT * from TableX where [BeforeDate] >= #" & _
datBefore & "# AND #" [AfterDate] <= #" & _
datAfter & "#"

' Now here is the VBA code to open the report. Note that the
' strSQL is the last item and is positioned in the 'WHERE'
section
' of the OpenReport command which is used to limit the
records of the report.
' The report should be setup to show all records from the
same base source initially

DoCmd.OpenReport stDocName, acPreview, , strSQL

Is this what you were looking for?

Gary Miller


VM said:
Yes.
The report, when you double-click on it, asks for the BeforeDate and
AfterDate (because the SQL query in the Access SQL view has
[Forms]![UnivCriteriaFrm]![txtFrom] and [Forms]![UnivCriteriaFrm]![txtTo]).
I'd like to be able to (literally) move this SQL statement into VBA. The
only difference would be that, instead of using
[Forms]![UnivCriteriaFrm]![txtFrom], I'll be referring to the VB textboxes
(me.BeforeDate.Value ).
Then, when I get the recordset from this query, I'd like to be able to
display it in the report.

What I'm not sure of is how to take that recordset and "move" it into the
report.

Vaughn

It all depends what you want to do with it. Open a recordset
maybe? You may need to give some more details of what you
are trying to do.

Do be aware that you are still missing the critical '#'
signs needed for date values.

Gary Miller
Sisters, OR

to
execute a report I textboxes
and the exe "After"
date, and I'd like SQL
statement based on (for
me) is that the
Click to expand...
Click to expand...
 
That's what I needed.
I did notice that the result of my query, when running from VBA, is
different than the result of the exact query when running it from the Access
Sql view.

VM

Gary Miller said:
Gotcha! Actually, you were very close at the beginning.
Bogdan gave you one method. Here is another that would be
good for opening it from a command button directly on your
form.

Dim strDocName As String, strSQL as String
Dim datBefore as Date, datAfter as Date

' Pick up the dates from your form. You will want some
' error handling in case they are empty

datBefore = Me.BeforeDate.Value
datAfter = Me.AfterDate.Value

strDocName = "rptYourReportName"

' Here is your SQL string
strSQL = "SELECT * from TableX where [BeforeDate] >= #" & _
datBefore & "# AND #" [AfterDate] <= #" & _
datAfter & "#"

' Now here is the VBA code to open the report. Note that the
' strSQL is the last item and is positioned in the 'WHERE'
section
' of the OpenReport command which is used to limit the
records of the report.
' The report should be setup to show all records from the
same base source initially

DoCmd.OpenReport stDocName, acPreview, , strSQL

Is this what you were looking for?

Gary Miller


VM said:
Yes.
The report, when you double-click on it, asks for the BeforeDate and
AfterDate (because the SQL query in the Access SQL view has
[Forms]![UnivCriteriaFrm]![txtFrom] and [Forms]![UnivCriteriaFrm]![txtTo]).
I'd like to be able to (literally) move this SQL statement into VBA. The
only difference would be that, instead of using
[Forms]![UnivCriteriaFrm]![txtFrom], I'll be referring to the VB textboxes
(me.BeforeDate.Value ).
Then, when I get the recordset from this query, I'd like to be able to
display it in the report.

What I'm not sure of is how to take that recordset and "move" it into the
report.

Vaughn

How would I incorporate this query into the VB Code?

It all depends what you want to do with it. Open a recordset
maybe? You may need to give some more details of what you
are trying to do.

Do be aware that you are still missing the critical '#'
signs needed for date values.

Gary Miller
Sisters, OR

Thanks for your help.
But if I choose to use the whole query, how would I do it?

In my case, it'd be:
"Select * from TableX where BeforeDate >= " &
me.BeforeDate.Value & " and
AfterDate <= " & me.AfterDate.Value

How would I incorporate this query into the VB Code?

Thanks again.
VM



I'm new at VBA and I wanted to know how I'd be able to
execute a report I
already created through a simple form with two textboxes
and the exe
cution
button.
Basically, the report asks for a "Before" and "After"
date, and I'd like
to
be able to create a form that:
1) Has two textboxes: BeforeDate and AfterDate
2) Has a button that, when pressed, runs a specific SQL
statement based on
the Entered dates and sends the recordset to the report.

The form design is pretty easy. The only hard thing (for
me) is that the
form interact with the report.

Thanks,
VM
Click to expand...
Click to expand...
Click to expand...
 
The syntax could be different. The query builder will put in
some more parenthesis, brackets and the like as well as
fuller references to the tables on each field.

Are the 'results' of the recordset different? Shouldn't be.
If there is, copy and post both sets of SQL.

Gary Miller

VM said:
That's what I needed.
I did notice that the result of my query, when running from VBA, is
different than the result of the exact query when running it from the Access
Sql view.

VM

Gotcha! Actually, you were very close at the beginning.
Bogdan gave you one method. Here is another that would be
good for opening it from a command button directly on your
form.

Dim strDocName As String, strSQL as String
Dim datBefore as Date, datAfter as Date

' Pick up the dates from your form. You will want some
' error handling in case they are empty

datBefore = Me.BeforeDate.Value
datAfter = Me.AfterDate.Value

strDocName = "rptYourReportName"

' Here is your SQL string
strSQL = "SELECT * from TableX where [BeforeDate] >= #" & _
datBefore & "# AND #" [AfterDate] <= #" & _
datAfter & "#"

' Now here is the VBA code to open the report. Note that the
' strSQL is the last item and is positioned in the 'WHERE'
section
' of the OpenReport command which is used to limit the
records of the report.
' The report should be setup to show all records from the
same base source initially

DoCmd.OpenReport stDocName, acPreview, , strSQL

Is this what you were looking for?

Gary Miller


VM said:
Yes.
The report, when you double-click on it, asks for the BeforeDate and
AfterDate (because the SQL query in the Access SQL
Click to expand...
view
has
[Forms]![UnivCriteriaFrm]![txtFrom] and [Forms]![UnivCriteriaFrm]![txtTo]).
I'd like to be able to (literally) move this SQL
Click to expand...
statement
into VBA. The
only difference would be that, instead of using
[Forms]![UnivCriteriaFrm]![txtFrom], I'll be referring
Click to expand...
to
the VB textboxes
(me.BeforeDate.Value ).
Then, when I get the recordset from this query, I'd
Click to expand...
like
to be able to
display it in the report.

What I'm not sure of is how to take that recordset and "move" it into the
report.

Vaughn

How would I incorporate this query into the VB Code?

It all depends what you want to do with it. Open a recordset
maybe? You may need to give some more details of
Click to expand...
what
you
are trying to do.

Do be aware that you are still missing the critical '#'
signs needed for date values.

Gary Miller
Sisters, OR

Thanks for your help.
But if I choose to use the whole query, how would
Click to expand...
I do
it?
In my case, it'd be:
"Select * from TableX where BeforeDate >= " &
me.BeforeDate.Value & " and
AfterDate <= " & me.AfterDate.Value

How would I incorporate this query into the VB Code?

Thanks again.
VM



I'm new at VBA and I wanted to know how I'd be
Click to expand...
able
to
execute a report I
already created through a simple form with two textboxes
and the exe
cution
button.
Basically, the report asks for a "Before" and "After"
date, and I'd like
to
be able to create a form that:
1) Has two textboxes: BeforeDate and AfterDate
2) Has a button that, when pressed, runs a
Click to expand...
specific
SQL
statement based on
the Entered dates and sends the recordset to the report.

The form design is pretty easy. The only hard
Click to expand...
thing
(for
me) is that the
form interact with the report.

Thanks,
VM
Click to expand...
Click to expand...
Click to expand...
 
They're exactly the same.

This is the query in the Access SQL view (I copy/pasted from the VBA form to
the SQL view):

SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#] FROM Assets LEFT JOIN (SELECT * FROM History WHERE
TranDate >= #1/1/2003# AND TranDate <= #2/1/2003# ) AS T ON Assets.AssetNo
= T.AssetNo WHERE Assets.Purch_Date >= #1/1/2003# And Assets.Purch_Date<=
#2/1/2003# AND T.AssetNo Is Null
_________________________________
This is the code in VBA with the *exact* sql query :
Dim strDocName As String, strSQL As String
Dim datBefore As Date, datAfter As Date

datBefore = Me.BeforeDate.Value
datAfter = Me.AfterDate.Value

strDocName = "AssetsNotInventoriedReport_2"

strSQL = "SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#] FROM Assets LEFT JOIN (SELECT * FROM History WHERE
TranDate >= #1/1/2003# AND TranDate <= #2/1/2003# ) AS T ON Assets.AssetNo
= T.AssetNo WHERE Assets.Purch_Date >= #1/1/2003# And Assets.Purch_Date<=
#2/1/2003# AND T.AssetNo Is Null"

DoCmd.OpenReport "AssetsNotInventoriedReport_2", acViewPreview, , strSQL


In the SQL view, it'll bring me the correct recordset. When running through
VBA I'll get the error 3306: "You have written a subquery that can return
more than one field without using the EXISTS reserved word in the main
query's FROM clause. Revise the SELECT statement of the subquery to request
only one field."

Note: While typing the error I did notice that the error tells me that my
subquery should only request only *one* field. But that would mean that the
engine that runs my VBA query is different than the Jet engine that runs my
Access query.

Vaughn


Gary Miller said:
The syntax could be different. The query builder will put in
some more parenthesis, brackets and the like as well as
fuller references to the tables on each field.

Are the 'results' of the recordset different? Shouldn't be.
If there is, copy and post both sets of SQL.

Gary Miller

VM said:
That's what I needed.
I did notice that the result of my query, when running from VBA, is
different than the result of the exact query when running it from the Access
Sql view.

VM

Gotcha! Actually, you were very close at the beginning.
Bogdan gave you one method. Here is another that would be
good for opening it from a command button directly on your
form.

Dim strDocName As String, strSQL as String
Dim datBefore as Date, datAfter as Date

' Pick up the dates from your form. You will want some
' error handling in case they are empty

datBefore = Me.BeforeDate.Value
datAfter = Me.AfterDate.Value

strDocName = "rptYourReportName"

' Here is your SQL string
strSQL = "SELECT * from TableX where [BeforeDate] >= #" & _
datBefore & "# AND #" [AfterDate] <= #" & _
datAfter & "#"

' Now here is the VBA code to open the report. Note that the
' strSQL is the last item and is positioned in the 'WHERE'
section
' of the OpenReport command which is used to limit the
records of the report.
' The report should be setup to show all records from the
same base source initially

DoCmd.OpenReport stDocName, acPreview, , strSQL

Is this what you were looking for?

Gary Miller


Yes.
The report, when you double-click on it, asks for the
BeforeDate and
AfterDate (because the SQL query in the Access SQL view
has
[Forms]![UnivCriteriaFrm]![txtFrom] and
[Forms]![UnivCriteriaFrm]![txtTo]).
I'd like to be able to (literally) move this SQL statement
into VBA. The
only difference would be that, instead of using
[Forms]![UnivCriteriaFrm]![txtFrom], I'll be referring to
the VB textboxes
(me.BeforeDate.Value ).
Then, when I get the recordset from this query, I'd like
to be able to
display it in the report.

What I'm not sure of is how to take that recordset and
"move" it into the
report.

Vaughn

message
How would I incorporate this query into the VB Code?

It all depends what you want to do with it. Open a
recordset
maybe? You may need to give some more details of what
you
are trying to do.

Do be aware that you are still missing the critical '#'
signs needed for date values.

Gary Miller
Sisters, OR

Thanks for your help.
But if I choose to use the whole query, how would I do
it?

In my case, it'd be:
"Select * from TableX where BeforeDate >= " &
me.BeforeDate.Value & " and
AfterDate <= " & me.AfterDate.Value

How would I incorporate this query into the VB Code?

Thanks again.
VM



I'm new at VBA and I wanted to know how I'd be able
to
execute a report I
already created through a simple form with two
textboxes
and the exe
cution
button.
Basically, the report asks for a "Before" and
"After"
date, and I'd like
to
be able to create a form that:
1) Has two textboxes: BeforeDate and AfterDate
2) Has a button that, when pressed, runs a specific
SQL
statement based on
the Entered dates and sends the recordset to the
report.

The form design is pretty easy. The only hard thing
(for
me) is that the
form interact with the report.

Thanks,
VM
Click to expand...
Click to expand...
Click to expand...
 
Not positive that I see what is causing that message, but
your SQL structure is confusing to me, but then I confuse
easily. Not sure why you are aliasing as 'T' nor why you are
putting your TranDate criteria in the sub query. Would
something like this produce the same result?

SELECT Assets.AssetNo, Assets.User1, Assets.User2,
Assets.[Serial#], History.TransType, History.TransDate
FROM Assets
LEFT JOIN History
ON Assets.AssetNo = History.AssetNo
WHERE Assets.Purch_Date >= #1/1/2003# And
Assets.Purch_Date<= #2/1/2003# AND History.TranDate >=
#1/1/2003# AND History.TranDate <= #2/1/2003# AND
Assets.AssetNo Is Null

Also, not positive if it will make a difference, but VBA may
like this better...

AND IsNull(Assets.AssetNo)

Gary Miller

They're exactly the same.

This is the query in the Access SQL view (I copy/pasted from the VBA form to
the SQL view):

SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#] FROM Assets LEFT JOIN (SELECT * FROM History WHERE
TranDate >= #1/1/2003# AND TranDate <= #2/1/2003# ) AS T ON Assets.AssetNo
= T.AssetNo WHERE Assets.Purch_Date >= #1/1/2003# And Assets.Purch_Date<=
#2/1/2003# AND T.AssetNo Is Null
_________________________________
This is the code in VBA with the *exact* sql query :
Dim strDocName As String, strSQL As String
Dim datBefore As Date, datAfter As Date

datBefore = Me.BeforeDate.Value
datAfter = Me.AfterDate.Value

strDocName = "AssetsNotInventoriedReport_2"

strSQL = "SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#] FROM Assets LEFT JOIN (SELECT * FROM History WHERE
TranDate >= #1/1/2003# AND TranDate <= #2/1/2003# ) AS T ON Assets.AssetNo
= T.AssetNo WHERE Assets.Purch_Date >= #1/1/2003# And Assets.Purch_Date<=
#2/1/2003# AND T.AssetNo Is Null"

DoCmd.OpenReport "AssetsNotInventoriedReport_2", acViewPreview, , strSQL


In the SQL view, it'll bring me the correct recordset. When running through
VBA I'll get the error 3306: "You have written a subquery that can return
more than one field without using the EXISTS reserved word in the main
query's FROM clause. Revise the SELECT statement of the subquery to request
only one field."

Note: While typing the error I did notice that the error tells me that my
subquery should only request only *one* field. But that would mean that the
engine that runs my VBA query is different than the Jet engine that runs my
Access query.

Vaughn


The syntax could be different. The query builder will put in
some more parenthesis, brackets and the like as well as
fuller references to the tables on each field.

Are the 'results' of the recordset different? Shouldn't be.
If there is, copy and post both sets of SQL.

Gary Miller

VM said:
That's what I needed.
I did notice that the result of my query, when running from VBA, is
different than the result of the exact query when
Click to expand...
running
it from the Access
Sql view.

VM

Gotcha! Actually, you were very close at the beginning.
Bogdan gave you one method. Here is another that
Click to expand...
would
be
good for opening it from a command button directly
Click to expand...
on
your
form.

Dim strDocName As String, strSQL as String
Dim datBefore as Date, datAfter as Date

' Pick up the dates from your form. You will want some
' error handling in case they are empty

datBefore = Me.BeforeDate.Value
datAfter = Me.AfterDate.Value

strDocName = "rptYourReportName"

' Here is your SQL string
strSQL = "SELECT * from TableX where [BeforeDate] >=
Click to expand...
#"
& _
datBefore & "# AND #" [AfterDate] <= #" & _
datAfter & "#"

' Now here is the VBA code to open the report. Note
Click to expand...
that
the
' strSQL is the last item and is positioned in the 'WHERE'
section
' of the OpenReport command which is used to limit the
records of the report.
' The report should be setup to show all records
Click to expand...
from
the
same base source initially

DoCmd.OpenReport stDocName, acPreview, , strSQL

Is this what you were looking for?

Gary Miller


Yes.
The report, when you double-click on it, asks for the
BeforeDate and
AfterDate (because the SQL query in the Access SQL view
has
[Forms]![UnivCriteriaFrm]![txtFrom] and
[Forms]![UnivCriteriaFrm]![txtTo]).
I'd like to be able to (literally) move this SQL statement
into VBA. The
only difference would be that, instead of using
[Forms]![UnivCriteriaFrm]![txtFrom], I'll be
Click to expand...
referring
to
the VB textboxes
(me.BeforeDate.Value ).
Then, when I get the recordset from this query,
Click to expand...
I'd
like
to be able to
display it in the report.

What I'm not sure of is how to take that recordset and
"move" it into the
report.

Vaughn

message
How would I incorporate this query into the VB Code?

It all depends what you want to do with it. Open a
recordset
maybe? You may need to give some more details of what
you
are trying to do.

Do be aware that you are still missing the
Click to expand...
critical
'#'
signs needed for date values.

Gary Miller
Sisters, OR

Thanks for your help.
But if I choose to use the whole query, how
Click to expand...
would
I do
it?

In my case, it'd be:
"Select * from TableX where BeforeDate >= " &
me.BeforeDate.Value & " and
AfterDate <= " & me.AfterDate.Value

How would I incorporate this query into the VB Code?

Thanks again.
VM



I'm new at VBA and I wanted to know how I'd
Click to expand...
be
able
to
execute a report I
already created through a simple form with two
textboxes
and the exe
cution
button.
Basically, the report asks for a "Before" and
"After"
date, and I'd like
to
be able to create a form that:
1) Has two textboxes: BeforeDate and AfterDate
2) Has a button that, when pressed, runs a specific
SQL
statement based on
the Entered dates and sends the recordset to the
report.

The form design is pretty easy. The only
Click to expand...
hard
thing
(for
me) is that the
form interact with the report.

Thanks,
VM
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Not sure if this helps, but the problem might be the following:

docmd.OpenReport's last parameter, the one you use strSql valiable for,
should be, from my knowledge, just the Where clause, which is used against
the query or table set as report's Recordsource.

If you pass strSQL as that parameter, Access will try to consider it a WHERE
clause, which should return a single value, true or false (so ONE field) as
the error
message sais.

If you want to use Gary's approach, you need to set the recordsource of the
report to the SQL statement WITHOUT Where clause, and then pass only the
Where clause (without the WHERE keyword) as last param in OpenReport
statement.

However, since you use the before and after date in two places (in the
subquery too), you cannot pass a single SQL WHERE clause to woek with both
queries involved (main and sub)

A simpler approach will be to use a global variable, call it cReportSQL
Before you open the report, you set that variable to your SQL statement,
then call the report without any SQL statement.

In Report_Open, you just have to

me.recordsource = cReportSQL


Regards,
Bogdan
________________________________________
Freelance programmer



They're exactly the same.

This is the query in the Access SQL view (I copy/pasted from the VBA form to
the SQL view):

SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#] FROM Assets LEFT JOIN (SELECT * FROM History WHERE
TranDate >= #1/1/2003# AND TranDate <= #2/1/2003# ) AS T ON Assets.AssetNo
= T.AssetNo WHERE Assets.Purch_Date >= #1/1/2003# And Assets.Purch_Date<=
#2/1/2003# AND T.AssetNo Is Null
_________________________________
This is the code in VBA with the *exact* sql query :
Dim strDocName As String, strSQL As String
Dim datBefore As Date, datAfter As Date

datBefore = Me.BeforeDate.Value
datAfter = Me.AfterDate.Value

strDocName = "AssetsNotInventoriedReport_2"

strSQL = "SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#] FROM Assets LEFT JOIN (SELECT * FROM History WHERE
TranDate >= #1/1/2003# AND TranDate <= #2/1/2003# ) AS T ON Assets.AssetNo
= T.AssetNo WHERE Assets.Purch_Date >= #1/1/2003# And Assets.Purch_Date<=
#2/1/2003# AND T.AssetNo Is Null"

DoCmd.OpenReport "AssetsNotInventoriedReport_2", acViewPreview, , strSQL


In the SQL view, it'll bring me the correct recordset. When running through
VBA I'll get the error 3306: "You have written a subquery that can return
more than one field without using the EXISTS reserved word in the main
query's FROM clause. Revise the SELECT statement of the subquery to request
only one field."

Note: While typing the error I did notice that the error tells me that my
subquery should only request only *one* field. But that would mean that the
engine that runs my VBA query is different than the Jet engine that runs my
Access query.

Vaughn


Gary Miller said:
The syntax could be different. The query builder will put in
some more parenthesis, brackets and the like as well as
fuller references to the tables on each field.

Are the 'results' of the recordset different? Shouldn't be.
If there is, copy and post both sets of SQL.

Gary Miller

VM said:
That's what I needed.
I did notice that the result of my query, when running from VBA, is
different than the result of the exact query when running it from the Access
Sql view.

VM

Gotcha! Actually, you were very close at the beginning.
Bogdan gave you one method. Here is another that would be
good for opening it from a command button directly on your
form.

Dim strDocName As String, strSQL as String
Dim datBefore as Date, datAfter as Date

' Pick up the dates from your form. You will want some
' error handling in case they are empty

datBefore = Me.BeforeDate.Value
datAfter = Me.AfterDate.Value

strDocName = "rptYourReportName"

' Here is your SQL string
strSQL = "SELECT * from TableX where [BeforeDate] >= #" & _
datBefore & "# AND #" [AfterDate] <= #" & _
datAfter & "#"

' Now here is the VBA code to open the report. Note that the
' strSQL is the last item and is positioned in the 'WHERE'
section
' of the OpenReport command which is used to limit the
records of the report.
' The report should be setup to show all records from the
same base source initially

DoCmd.OpenReport stDocName, acPreview, , strSQL

Is this what you were looking for?

Gary Miller


Yes.
The report, when you double-click on it, asks for the
BeforeDate and
AfterDate (because the SQL query in the Access SQL view
has
[Forms]![UnivCriteriaFrm]![txtFrom] and
[Forms]![UnivCriteriaFrm]![txtTo]).
I'd like to be able to (literally) move this SQL statement
into VBA. The
only difference would be that, instead of using
[Forms]![UnivCriteriaFrm]![txtFrom], I'll be referring to
the VB textboxes
(me.BeforeDate.Value ).
Then, when I get the recordset from this query, I'd like
to be able to
display it in the report.

What I'm not sure of is how to take that recordset and
"move" it into the
report.

Vaughn

message
How would I incorporate this query into the VB Code?

It all depends what you want to do with it. Open a
recordset
maybe? You may need to give some more details of what
you
are trying to do.

Do be aware that you are still missing the critical '#'
signs needed for date values.

Gary Miller
Sisters, OR

Thanks for your help.
But if I choose to use the whole query, how would I do
it?

In my case, it'd be:
"Select * from TableX where BeforeDate >= " &
me.BeforeDate.Value & " and
AfterDate <= " & me.AfterDate.Value

How would I incorporate this query into the VB Code?

Thanks again.
VM



I'm new at VBA and I wanted to know how I'd be able
to
execute a report I
already created through a simple form with two
textboxes
and the exe
cution
button.
Basically, the report asks for a "Before" and
"After"
date, and I'd like
to
be able to create a form that:
1) Has two textboxes: BeforeDate and AfterDate
2) Has a button that, when pressed, runs a specific
SQL
statement based on
the Entered dates and sends the recordset to the
report.

The form design is pretty easy. The only hard thing
(for
me) is that the
form interact with the report.

Thanks,
VM
Click to expand...
Click to expand...
Click to expand...
 
Good point Bogdan. I was getting a little bogged down with
all different variations being discussed and his wanting the
full SQL syntax. Gave him too much for the OpenReport.

Thanks,

Gary

Bogdan Zamfir said:
Hi,

Not sure if this helps, but the problem might be the following:

docmd.OpenReport's last parameter, the one you use strSql valiable for,
should be, from my knowledge, just the Where clause, which is used against
the query or table set as report's Recordsource.

If you pass strSQL as that parameter, Access will try to consider it a WHERE
clause, which should return a single value, true or false (so ONE field) as
the error
message sais.

If you want to use Gary's approach, you need to set the recordsource of the
report to the SQL statement WITHOUT Where clause, and then pass only the
Where clause (without the WHERE keyword) as last param in OpenReport
statement.

However, since you use the before and after date in two places (in the
subquery too), you cannot pass a single SQL WHERE clause to woek with both
queries involved (main and sub)

A simpler approach will be to use a global variable, call it cReportSQL
Before you open the report, you set that variable to your SQL statement,
then call the report without any SQL statement.

In Report_Open, you just have to

me.recordsource = cReportSQL


Regards,
Bogdan
________________________________________
Freelance programmer



<Jim> wrote in message news:OM%[email protected]...
They're exactly the same.

This is the query in the Access SQL view (I copy/pasted
Click to expand...
from the VBA form
to
the SQL view):

SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#] FROM Assets LEFT JOIN (SELECT * FROM History WHERE
TranDate >= #1/1/2003# AND TranDate <= #2/1/2003# ) AS
Click to expand...
T ON
Assets.AssetNo
= T.AssetNo WHERE Assets.Purch_Date >= #1/1/2003# And Assets.Purch_Date<=
#2/1/2003# AND T.AssetNo Is Null
_________________________________
This is the code in VBA with the *exact* sql query :
Dim strDocName As String, strSQL As String
Dim datBefore As Date, datAfter As Date

datBefore = Me.BeforeDate.Value
datAfter = Me.AfterDate.Value

strDocName = "AssetsNotInventoriedReport_2"

strSQL = "SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#] FROM Assets LEFT JOIN (SELECT * FROM History WHERE
TranDate >= #1/1/2003# AND TranDate <= #2/1/2003# ) AS
Click to expand...
T ON
Assets.AssetNo
= T.AssetNo WHERE Assets.Purch_Date >= #1/1/2003# And Assets.Purch_Date<=
#2/1/2003# AND T.AssetNo Is Null"

DoCmd.OpenReport "AssetsNotInventoriedReport_2", acViewPreview, , strSQL


In the SQL view, it'll bring me the correct recordset.
Click to expand...
When running
through
VBA I'll get the error 3306: "You have written a subquery that can return
more than one field without using the EXISTS reserved word in the main
query's FROM clause. Revise the SELECT statement of the
Click to expand...
subquery to
request
only one field."

Note: While typing the error I did notice that the error tells me that my
subquery should only request only *one* field. But that
Click to expand...
would mean that
the
engine that runs my VBA query is different than the Jet
Click to expand...
engine that runs
my
Access query.

Vaughn


The syntax could be different. The query builder will put in
some more parenthesis, brackets and the like as well as
fuller references to the tables on each field.

Are the 'results' of the recordset different? Shouldn't be.
If there is, copy and post both sets of SQL.

Gary Miller

That's what I needed.
I did notice that the result of my query, when running
from VBA, is
different than the result of the exact query when running
it from the Access
Sql view.

VM

message
Gotcha! Actually, you were very close at the beginning.
Bogdan gave you one method. Here is another that would
be
good for opening it from a command button directly on
your
form.

Dim strDocName As String, strSQL as String
Dim datBefore as Date, datAfter as Date

' Pick up the dates from your form. You will want some
' error handling in case they are empty

datBefore = Me.BeforeDate.Value
datAfter = Me.AfterDate.Value

strDocName = "rptYourReportName"

' Here is your SQL string
strSQL = "SELECT * from TableX where [BeforeDate] = #"
& _
datBefore & "# AND #" [AfterDate] <= #" & _
datAfter & "#"

' Now here is the VBA code to open the report. Note that
the
' strSQL is the last item and is positioned in the
'WHERE'
section
' of the OpenReport command which is used to limit the
records of the report.
' The report should be setup to show all records from
the
same base source initially

DoCmd.OpenReport stDocName, acPreview, , strSQL

Is this what you were looking for?

Gary Miller


Yes.
The report, when you double-click on it, asks for the
BeforeDate and
AfterDate (because the SQL query in the Access SQL
view
has
[Forms]![UnivCriteriaFrm]![txtFrom] and
[Forms]![UnivCriteriaFrm]![txtTo]).
I'd like to be able to (literally) move this SQL
statement
into VBA. The
only difference would be that, instead of using
[Forms]![UnivCriteriaFrm]![txtFrom], I'll be referring
to
the VB textboxes
(me.BeforeDate.Value ).
Then, when I get the recordset from this query, I'd
like
to be able to
display it in the report.

What I'm not sure of is how to take that recordset and
"move" it into the
report.

Vaughn

message
How would I incorporate this query into the VB
Code?

It all depends what you want to do with it. Open a
recordset
maybe? You may need to give some more details of
what
you
are trying to do.

Do be aware that you are still missing the critical
'#'
signs needed for date values.

Gary Miller
Sisters, OR

Thanks for your help.
But if I choose to use the whole query, how would
I do
it?

In my case, it'd be:
"Select * from TableX where BeforeDate >= " &
me.BeforeDate.Value & " and
AfterDate <= " & me.AfterDate.Value

How would I incorporate this query into the VB
Code?

Thanks again.
VM



I'm new at VBA and I wanted to know how I'd be
able
to
execute a report I
already created through a simple form with two
textboxes
and the exe
cution
button.
Basically, the report asks for a "Before" and
"After"
date, and I'd like
to
be able to create a form that:
1) Has two textboxes: BeforeDate and AfterDate
2) Has a button that, when pressed, runs a
specific
SQL
statement based on
the Entered dates and sends the recordset to the
report.

The form design is pretty easy. The only hard
thing
(for
me) is that the
form interact with the report.

Thanks,
VM
Click to expand...
Click to expand...
Click to expand...
 
Back
Top