473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

VBA filtering vs Form filters

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
4 2167
FishVal
2,653 Expert 2GB
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
roryok
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
2,653 Expert 2GB
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
sierra7
446 Expert 256MB
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

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

Similar topics

13
by: kevinold | last post by:
Hello everyone, I have a list of about 1600 employees that I'd like to have displayed in a form. I'd like to make the "search" for the user as easy as possible. I ran across this:...
1
by: mstery | last post by:
I have a report generated via an ID selection made in a dropdown on a form. The report filters by an on click event in a preview report button on the form. Everything in the report, including...
0
by: Patrick | last post by:
I'm working on a contact management application, and need a hand with one aspect... Here's what I want to create: ------------------------------------ A form split into two parts. There is a...
7
by: | last post by:
Hello, Does anyone have an idea on how I can filter the data in the gridview control that was returned by an sql query? I have a gridview that works fine when I populate it with data. Now I...
19
by: Chantelle | last post by:
I've got this A2K DB that has a continuous form that lists Suppliers and their details. The form has a field for each supplier that holds several Keywords that reflect the suppliers products or...
3
by: Rick | last post by:
I have a customer order form with a subform that lists parts per customer order that I'm updating. I'm trying to filer the parts list per Manufacture and Model number so that the user doesn't have...
2
by: Katie | last post by:
I need to store a history of two fields in two seperate tables: Event - where the key is Event_ID Stage - where the key is Stage_ID The joins are: Main Table Event Table Stage Table ...
10
by: Redbeard | last post by:
Hi, I am a newbie using Access 2003. I am trying to apply a filter to a form and then re-filtering that forms records again. Basically I have my main form and when I wish to filter I click a button...
1
by: =?Utf-8?B?anAybXNmdA==?= | last post by:
I've got a ListBox on my Visual Studio C# form with several entries (14,000). I have to put a search field on the form. As text is entered into the search field, I want the ListBox to remove...
3
by: Joseph Geretz | last post by:
I'm using the Request Filter documentation which can be found here: http://msdn.microsoft.com/en-us/library/system.web.httprequest.filter.aspx In this example, two filters are installed, one...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.