pull VB variables (report msgbox) into a query 
Author Message
 pull VB variables (report msgbox) into a query

I've got this report and in the report it asks for a start
date and end date. It asks this inside a msgbox which is
VB controlled. I want to input those dates into my query,
that will then report the correct data. I don't know how
to refer to the VB data (what was inputted in the msgbox)
in my query.

Thanks in advance.



Tue, 22 Nov 2005 04:24:59 GMT  
 pull VB variables (report msgbox) into a query
Simply open the report with a where clause,a nd then you don't have to
modify the query, or make the query reference the form (it gets messy
anyway).

Hence, on you nice form that asks for the start/end date, you can place the
following code behind the button that launches the report:

   Dim strWhere      As String

   strWhere = "> " & Me.StartDate & " and < " & Me.EndDate
   strWhere = BuildCriteria("InvoiceDate", dbDate, strWhere)

   DoCmd.OpenReport "your reprot", acViewPreview, , strWhere

The above assumes a field of invoice date, but you can change the above to
whatever you want.

Using the "where" clause is nice, since then you don't have to modify
existing queries. For a bunch of sample screen shots that uses the above
idea, check out:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

--
Albert D. Kallal     (MVP)
Edmonton,  Alberta Canada

http://www.attcanada.net/~kallal.msn



Tue, 22 Nov 2005 04:41:59 GMT  
 pull VB variables (report msgbox) into a query
Thanks for the idea, but it's mostly what I already have.
In the code, behind what you have that launches the
report, when you are in the query in the where field how
do you access the date set aside in the 'strWhere'?

Quote:
>-----Original Message-----
>Simply open the report with a where clause,a nd then you
don't have to
>modify the query, or make the query reference the form
(it gets messy
>anyway).

>Hence, on you nice form that asks for the start/end date,
you can place the
>following code behind the button that launches the report:

>   Dim strWhere      As String

>   strWhere = "> " & Me.StartDate & " and < " & Me.EndDate
>   strWhere = BuildCriteria("InvoiceDate", dbDate,
strWhere)

>   DoCmd.OpenReport "your reprot", acViewPreview, ,
strWhere

>The above assumes a field of invoice date, but you can
change the above to
>whatever you want.

>Using the "where" clause is nice, since then you don't
have to modify
>existing queries. For a bunch of sample screen shots that
uses the above
>idea, check out:

>http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.htm
l

>--
>Albert D. Kallal     (MVP)
>Edmonton,  Alberta Canada

>http://www.attcanada.net/~kallal.msn

>.



Tue, 22 Nov 2005 20:26:23 GMT  
 pull VB variables (report msgbox) into a query
You don't have to do anything to the query. As long as the query has all the
fields used in the where clause, you are done. This approach also works when
you open a form with a where clause also.

This means as explained that the query has NO conditions in it, and you are
providing all of them via the where clause. This makes for clean quires
since then the query DOES NOT have conditions that reference the form
directly.

--
Albert D. Kallal     (MVP)
Edmonton,  Alberta Canada

http://www.attcanada.net/~kallal.msn



Wed, 23 Nov 2005 01:41:02 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Passing queries/variables from VB to Crytal Report.

2. HELP with MS Report Writer and report not pulling current data

3. Variable in a Crystal Report Database Query

4. Q: Pass a SQL query from VB to crystal report sub-report

5. Pulling Access97 data into Word97 variables

6. query to link or join/pull information from databases

7. Pulling and Sorting Data from Access Query

8. Sql query doesn`t narrow year --pulls in 00 and 01

9. Pull command query using RDA and SQLCE

10. Get variable from a count query into VB

11. Using VB variable in SQL query

12. variables in SQL queries VB front end to acess database

 

 
Powered by phpBB® Forum Software