Hi everybody,
i'm trying to write a selection query that filters a list of records which the "date" field value is BETWEEN a minimum and a maximum values. These values must be taken from a form IF they are not empty. If they are empty the query must return the whole unfilterd list.
I don't know how to insert an IF condition in a SELECT....WHERE function.
Many thanks for your attention
14  2410  
I'm not sure if you can accomplish this in an SQL Statement, and quite frankly, I'm not the one to ask. What you can do is to dynamically create a Query, conditionally building the SQL Source Statement based on whether or not a Form Field named txtDOB is Null or not. The following Code will dynamically create a Query named 'New Query' based on an Employee Table named tblEmployees. If a Date of Birth Field ([txtDOB]) Is Null, then all Records are returned from tblEmployees. If the Date of Birth Field is Not Null, then all Employee Records whose whose date of Birth falls between 1/1/1970 and 12/31/1980 are returned. - On Error Resume Next
-
Dim strSQL As String
-
Dim qdfNew As DAO.QueryDef
-
-
'DELETE the pre-existing Query (may/may not exist)
-
CurrentDb.QueryDefs.Delete "New Query"
-
-
If IsNull(Me![txtDOB]) Then
-
strSQL = "SELECT * FROM tblEmployees;"
-
Else
-
strSQL = "SELECT * FROM tblEmployees WHERE [DOB] BETWEEN #1/1/1970# AND #12/31/1980#;"
-
End If
-
-
Set qdfNew = CurrentDb.CreateQueryDef("New Query", strSQL)
MMMh.. i tried this as you suggested but the new query is always empty, is it a problem with a too long string or what else? -
On Error Resume Next
-
Dim codiceSQL As String
-
Dim qdfNew As DAO.QueryDef
-
-
'DELETE the pre-existing Query (may/may not exist)
-
CurrentDb.QueryDefs.Delete "Estrazione"
-
-
If (IsNull(Me![Forms]![Estrazione]![Text9]) And IsNull(Me![Forms]![Estrazione]![Text11])) Then
-
codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20])AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND ((Records.ID_categoria2) Like " * " & [Forms]![Estrazione]![Combo7]));"
-
Else
-
If (IsNull(Me![Forms]![Estrazione]![Text9]) And Not IsNull(Me![Forms]![Estrazione]![Text11])) Then
-
-
codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20]) AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND ((Records.ID_categoria2) Like " * " & [Forms]![Estrazione]![Combo7]) AND ((Records.Scadenza)<[Forms]![Estrazione]![text11]));"
-
Else
-
If (Not IsNull(Me![Forms]![Estrazione]![Text9]) And IsNull(Me![Forms]![Estrazione]![Text11])) Then
-
-
codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20]) AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND ((Records.ID_categoria2) Like " * " & [Forms]![Estrazione]![Combo7]) AND ((Records.Scadenza)>[Forms]![Estrazione]![text9]));"
-
-
Else
-
-
codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20]) AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND ((Records.ID_categoria2) Like " * " &[Forms]![Estrazione]![Combo7]) AND ((Records.Scadenza) BETWEEN [Forms]![Estrazione]![text9] AND [Forms]![Estrazione]![text11]));"
-
-
End If
-
End If
-
-
End If
-
-
Set qdfNew = CurrentDb.CreateQueryDef("Estrazione", codiceSQL)
-
Mihail, thank you but i don't think i can understand that post.. i'm quite a noob. And I hope to find something easier :)
Line #9 : Else... What ?
Try this code instead yours: - If IsNull(Me![Forms]![Estrazione]![Text9]) Then
-
If IsNull(Me![Forms]![Estrazione]![Text11]) Then
-
codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20])AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND ((Records.ID_categoria2) Like " * " & [Forms]![Estrazione]![Combo7]));"
-
Else
-
codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20]) AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND ((Records.ID_categoria2) Like " * " & [Forms]![Estrazione]![Combo7]) AND ((Records.Scadenza)<[Forms]![Estrazione]![text11]));"
-
End If
-
Else
-
If IsNull(Me![Forms]![Estrazione]![Text11]) Then
-
codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20]) AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND ((Records.ID_categoria2) Like " * " & [Forms]![Estrazione]![Combo7]) AND ((Records.Scadenza)>[Forms]![Estrazione]![text9]));"
-
Else
-
codiceSQL = "SELECT * FROM Records WHERE (((Records.Persona_coinvolta_1) Like " * " & [Forms]![Estrazione]![Combo0]) AND ((Records.[Fatto_SI/NO]) Like " * " & [Forms]![Estrazione]![Check20]) AND ((Records.ID_categoria1) Like " * " & [Forms]![Estrazione]![Combo5]) AND ((Records.ID_categoria2) Like " * " &[Forms]![Estrazione]![Combo7]) AND ((Records.Scadenza) BETWEEN [Forms]![Estrazione]![text9] AND [Forms]![Estrazione]![text11]));"
-
End If
-
End If
Thank you Mihail, it doesn't give me any error but the final new query "Estrazione" is still empty (SQL code that i find is: "SELECT;").. :(
is this correct?
Set qdfNew = CurrentDb.CreateQueryDef("Estrazione", codiceSQL)
While I don't know SQL, I can't debug your code.
I only correct a little bit your "grammar" based on ADezii's logic and your code.
Read this article: http://bytes.com/topic/access/insigh...vba-1-overview
understand it and try to see how is changed your codiceSQL string after every line where it appear. (After lines 5, 9, 11) and/or at the end, after line 13.
Ok, the problem is: SQL= ".... Like "*" ....;" I can't use it in vba. Alternatives?
And % or _ are not accepted, they give errors. It's strange 'cause " * " doesn't give error, but the query is created empty :S
My way to use SQL in VBA (but I try to avoid that as more as I can) is to create an working query using Query Design interface.
Of course I use real criteria to filter my query.
After I obtain a good set of records based on this real criteria I switch to SQL View for query, I copy the SQL created by Access and paste it in VBA editor. After that I replace the real values for filter with custom values (strings).
This way I am sure that the entire syntax for SQL is OK (Access do not make mistakes when create SQL strings). So, if an error appear only my few custom strings are faulty. Usually by forget a space or to double the quotes.
Remember that a SQL string is a single row string even if Access use multiple lines to show it.
So my practice is to paste this string in VBA editor two times. First one is only for a backup. The second one is the string I work with.
After I make some changes in the second string I copy it and paste back in query SQL and I run the query. If all is OK then my new string is Ok so I can continue work with it.
The last thing is to replace real filter values with my string variables.
Well, I know that my English is not very good but I hope you understand (at least) the idea.
NeoPa 32,556
Expert Mod 16PB
Here is a query that will achieve what you want - SELECT t2.recid, t2.EmployeeName, t2.targetDate
-
FROM t2
-
WHERE (((t2.targetDate)>=nz([Forms]![Form1]![fromDate],[targetDate]) And (t2.targetDate)<=nz([Forms]![Form1]![toDate),[targetDate])));
The problem were the quotes " . In VBA they must be ' * ' instead of " * ". It works.
Thank you everybody guys, you've been very kind and usefull
@ChrisPadgham i'll study how to use this nz funcion to semplify very much my code, thanks
NeoPa 32,556
Expert Mod 16PB Sweeda:
The problem were the quotes " . In VBA they must be ' * ' instead of " * ". It works.
I wouldn't state it quite as simply as that, but fundamentally, that understanding will help you get past the problem at least ;-)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Alison Givens |
last post by:
Hi,
I want two Session values in my record selection query. The come out of an
other webform.
When I do it hardcoded, it works.
How dow I do this with the Session values?
Hardcoded:
Dim...
|
by: Axel |
last post by:
Hi,
a question about something that seems very simple at first glance:
is it possible to reference other controls of a subform in a query
window without referencing through the parent form? I...
|
by: umangjaipuria |
last post by:
I have two job queues doing complimentary work and writing their output
once a minute into a file. The files for each minute have to processed
in pairs - one from the first job queue and one from...
|
by: chrisek |
last post by:
Hello,
I have 2 tables:
- Customers with columns:
customerID(prim_key),
customerName(with customer's names)
- Deliveries with columns:
deliveryID(primKey),
sender(ref_key to CustomerID from...
|
by: Srini |
last post by:
Does anyone know if we can do SELECTIVE unload/load data from/to a
Tablespace using Where condition on z/OS with IBM DB2 UNLOAD / LOAD
utility?
Thanks,
Srini
|
by: diego |
last post by:
Greetings everyone!
Is there a way to stop query analyzer from processing remaining query
statements? Let's say i have the following query in query analyzer:
query statements
if condition...
|
by: Aldwin |
last post by:
Hi,
I have build a selectio query in MS access. The dat is comming from a ODBC with Fire Fox. No I need to add a record counter that shows a line number in each line.
Does any one knows a...
|
by: didihynes |
last post by:
Hi Guys,
I'm in desparate need of help. I am producing a database for my dissertation and have got majorly stuck. I am currently creating a form in which the user will select a student from a...
|
by: Yash |
last post by:
My SQL query is like:
SELECT .....
FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY ....(5 more
joins)
WHERE C.created_date between @start_date and @end_date
AND...
|
by: Patricia Pines |
last post by:
To recap:
I have 3 comboboxes. After making a selection in combobox 1, it updates combobox 2, and after making a selection in combobox 2, it updates combobox 3.
I have 7 Cat (Category)...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
