By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,192 Members | 1,784 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,192 IT Pros & Developers. It's quick & easy.

VBA filtering vs Form filters

P: 5
Hi,

(Access 2002 & Windows XP Pro)

I have a Select query and one of the columns has the following criteria: (from SQL view)
Expand|Select|Wrap|Line Numbers
  1. HAVING (((order.orderDate)>=[Forms]![Date Filter]![txtDateFrom] And (order.orderDate)<=[Forms]![Date Filter]![txtDateTo]));
This works OK when I'm using the Date Filter form with two unbound boxes which ask for the start and end dates. The query dynaset appears correctly.

I now want to introduce a form (lets call it Results) between the Date filter form (above) and the Select query so that it shows the results of the search rather than the query dynaset.

A couple of guys helped me yesterday to do something similar but with only one criteria value. Now I've got two to contend with in the same criteria I'm again tripping over. I've tried adapting what I previously used but without any useful results.

The Search button on the 'Date Filter' form has been changed so that it now has the following VBA code behind it:
Expand|Select|Wrap|Line Numbers
  1.     stDocName = "Results"
  2.     stLinkCriteria = <...I'm lost what to put here ...>
  3.     DoCmd.OpenForm stDocName, , , stLinkCriteria
or if it's best to use the Filter command on the Results form what would I need to put in there?

Could anyone suggest some tips on this, please?

Thanks
Nov 19 '07 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, roryok.

You may modify Form.RecordSource rather than Form.Filter property.
Expand|Select|Wrap|Line Numbers
  1.  
  2. stDocName = "Results"
  3. DoCmd.OpenForm stDocName
  4. Forms(stDocName).RecordSource=<... full SQL statement expected to return filtered records ...>
  5.  
Nov 19 '07 #2

P: 5
Thanks FishVal.

Following your suggestion I've put the whole SQL statement as below but getting "Syntax Errors" when I compile it in VBA.

To be honest, I've been playing around with the " and & symbols following the structure as shown in the VBA help topic.

Expand|Select|Wrap|Line Numbers
  1.       stDocName = "Orders by date form"
  2.       DoCmd.OpenForm stDocName
  3.       Forms(stDocName).RecordSource="SELECT order.PO_Nbr AS [PO Number], order.orderTitle AS Title, order.orderDate AS [Date Ordered], _
  4.       & order.orderCostCentre AS [Cost Centre], employee.employeeName AS [Who Placed Order], supplier.supplierName AS [Ordered From], _
  5.       order.orderRequestor AS [Who Requested Items], order.orderDelivered AS [Order Delivered?], company.companyName AS [From Company], _
  6.       Sum(suborder.suborderPrice) AS [Sub-Total], order.orderDeliveryCost AS [Delivery Costs], order.orderVAT AS VAT, Sum([suborderprice])+[orderdeliverycost]+[orderVAT] AS [Grand Total], _
  7.       order.orderInvoiceNumber AS [Invoice No], order.orderInvoiceSignedOff AS [Invoice Signed-off?]" _
  8.       & FROM supplier INNER JOIN ((employee INNER JOIN (company INNER JOIN [order] ON company.companyID = order.orderCompany) ON employee.employeeID = order.orderedBy) _
  9.       INNER JOIN suborder ON order.PO_Nbr = suborder.PO_Nbr2) ON supplier.supplierID = order.orderedFrom" _
  10.       & GROUP BY order.PO_Nbr, order.orderTitle, order.orderDate, order.orderCostCentre, employee.employeeName, supplier.supplierName, order.orderRequestor, _
  11.       order.orderDelivered, company.companyName, order.orderDeliveryCost, order.orderVAT, order.orderInvoiceNumber, order.orderInvoiceSignedOff" _
  12.       & HAVING (((order.orderDate)>=[Forms]![Date Filter]![txtDateFrom] And (order.orderDate)<=[Forms]![Date Filter]![txtDateTo]))
  13.  
However, have I gone over the top here?
Nov 19 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Check all your quotes.
Expression like
Expand|Select|Wrap|Line Numbers
  1. strVar = "qwertyuiopasdfghjklzxcvbnm"
  2.  
should be written in multiple lines in the following manner
Expand|Select|Wrap|Line Numbers
  1. strVar="qwertyuiop" & _
  2.     "asdfghjkl" & _
  3.     "zxcvbnm"
  4.  
as concatenation of substrings.
Be sure not to miss any character (space ;) for example).
A good practice is to add after the line where string variable is being set a line where it's content is sent to immediate window before it is being assigned to form property.
Expand|Select|Wrap|Line Numbers
  1. strSQL=.......
  2. Debug.Print strSQL
  3.  
then copypaste the output to query builder and run it to check for errors.
Nov 20 '07 #4

Expert 100+
P: 446
Hi Rory

I think what you are asking is 'what is the syntax for a compound query?'
It trivially easy! Take your existing code

Expand|Select|Wrap|Line Numbers
  1. HAVING (((order.orderDate)>=[Forms]![Date Filter]![txtDateFrom] And (order.orderDate)<=[Forms]![Date Filter]![txtDateTo]));
and use it as the basis of your criteria ;-

Expand|Select|Wrap|Line Numbers
  1. stLinkCriteria =" (([order.orderDate]>= #" & [Forms]![Date Filter]![txtDateFrom]  & " # And [order.orderDate]<=#" & [Forms]![Date Filter]![txtDateTo])) & "#"
Sorry, I thought it was trivially easy until I realised they were dates!

Notice that fields must be defined inside square brackets and the variable bits are concatonated using ampersand (&). You don't need the final semi-colon because this is not SQL. Dates must be embraced inside hashes (#) to be recognised as such.

If you are doing this from inside the form "Date Filter" then you can abbreviate the above by replacing [Forms]![Date Filter]![txtDateFrom] with Me.txtDateFrom, no suquare brackets needed, unless you prefer Me![txtDateFrom]

If you are in the USA this will be ok, but if you are in the UK you may find the dates are screwed-up, because Access will usually interpret your dates in the American format, regardless of country settings. So in any formula/equation using dates they should be formatted as follows;-

Expand|Select|Wrap|Line Numbers
  1.    . . . . &    Format(Me.txtDateTo,"yyyy-mm-dd")   & . . .
Don't be tempted to put in three 'm' (-mmm-) If you are using dates a lot it is better to define a global string variable as "yyyy-mm-dd" then

Expand|Select|Wrap|Line Numbers
  1.    . . . . &    Format(Me.txtDateTo, stDF)   & . . .
Hope this helps
Nov 21 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.