About Geoff Expertise I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.
See my
website for example apps and downloads
Experience I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.
Commercial database design and development work undertaken.
Question WHERE LN_SERVR_VNDR.DDA_VNDR_ID = '16386'
AND REO_CASE.DISPN_DATE BETWEEN '01-jan-2004' AND '01-jan-2005'
I would like to see if it is possible to create a form that will enter the LN_SERVR_VNDR.DDA_VNDR_ID and
REO_CASE.DISPN_DATE. There are a total of twelve pass thru queries and it is very time consuming to open each one and type in the id number and date ranges. If I had a form that would I enter in this data "once" then the form can populate the values into all the pass thru queries. This would be much more efficient. Also, as times I will need to enter two or more LN_SERVR_VNDR.DDA_VNDR_ID's so if you can help me with this I would greatly appreciate it. Thanks for your assistance.
Ray
Answer Hi Ray
Let assume the form name is frmMain and you have 3 text boxes, named: txtID, txtDateIn & txtDateOut.
The formal reference from within the Access DB would be:
Forms!frmMain.txtID etc.
So you can use this format in your query statement directly, ensuring that you handle the single quotes (') correctly...
You WHERE predicate would read something like..
WHERE LN_SERVR_VNDR.DDA_VNDR_ID = ' & Forms!frmMain.txtID & '
AND REO_CASE.DISPN_DATE BETWEEN ' & Forms!frmMain.txtDateIn & ' AND ' & Forms!frmMain.txtDateOut & '
as I said the only proviso now is ensuring the quotes are properly handled. The above format should work in the QBE or a form's property dialog, but in VBA code you will need to use double quotes in the form of:
...VNDR_ID = '" & Forms!frmMain.txtID & "' AND REO_CA....
