Hi,
I want to select multiple variables from a form and pass them to a query, however I don't mind how many of the variables are used.
I am currently using the syntax;
Like [Forms]![Form Name]![Combo Box Name] & "*"
On each of the fields in the query I want to be able to filter on for my report. However it is ignoring my null fields and given I have 10+ variables and none of them are required fields I am getting very few results. Is there any way to do this but to include nulls as well?
I am using XP and Access 2003.
Very long winded code below... - SELECT PunchListDetails.Ref, PunchListDetails.[Entered On], PunchListDetails.[Associated System], PunchListDetails.[Location/Area], PunchListDetails.PunchDetails, PunchListDetails.[Additional Comments], PunchListDetails.[Item Open], PunchListDetails.[Item Closed], PunchListDetails.[Item Raised At], PunchListDetails.[Date Closed Out], PunchListDetails.[Due On], PunchListDetails.[Revised Due Date], PunchListDetails.Discipline, PunchListDetails.[Punch Owner], PunchListDetails.[Responsible Contractor], PunchListDetails.OwnerSignOff, PunchListDetails.OwnerSignOffLogIn, PunchListDetails.OwnerSignOffDate, PunchListDetails.EngineerSignOff, PunchListDetails.EngineerSignOffLogIn, PunchListDetails.EngineerSignOffDate, PunchListDetails.ClientSignOff, PunchListDetails.ClientSignOffLogIn, PunchListDetails.ClientSignOffDate, PunchListDetails.[Project Number], PunchListDetails.Impact
-
FROM PunchListDetails
-
WHERE (((PunchListDetails.[Associated System]) Like [Forms]![ReportsForm]![cmbSystemCode] & "*") AND ((PunchListDetails.[Location/Area]) Like [Forms]![ReportsForm]![cmbArea] & "*") AND ((PunchListDetails.[Item Raised At]) Like [Forms]![ReportsForm]![cmbRaised] & "*") AND ((PunchListDetails.Discipline) Like [Forms]![ReportsForm]![cmbDiscipline] & "*") AND ((PunchListDetails.[Punch Owner]) Like [Forms]![ReportsForm]![cmbOwner] & "*") AND ((PunchListDetails.[Responsible Contractor]) Like [Forms]![ReportsForm]![cmbContractor] & "*") AND ((PunchListDetails.Impact) Like [Forms]![ReportsForm]![cmbImpact] & "*") AND ((PunchListDetails.[Entered By]) Like [Forms]![ReportsForm]![cmbRaisedBy] & "*") AND ((PunchListDetails.[Item Type]) Like [Forms]![ReportsForm]![cmbItemType] & "*"));
11 2212
Try using OR instead of AND in your WHERE clause.
That does work but it now returns values for rows not intended.
For example if we wanted raised by Damon Reid and Mechanical items it now returns all items raised by Damon Reid and all Mechanical items instead of the Mechanical items raised by Damon Reid.
I need to keep it as AND but I need to be able to include nulls in the "*" search.
Thanks for the try though.
I thought it was too easy!
Let me think about it, and I'll get back to you.
You need to build the criteria 'on the fly'.
Do this in an appropriate event.
Do you have a search button on your [ReportsForm]? - build criteria in OnClick event
Is this query the basis for a report/form? - build criteria in OnLoad event - Dim strCriteria As String
-
strCriteria=""
-
If Not IsNull([Forms]![ReportsForm]![cmbSystemCode]) Then
-
strCriteria=strCriteria & "PunchListDetails.[Associated System]) Like [Forms]![ReportsForm]![cmbSystemCode] & '*'" & " AND "
-
End if
-
If Not IsNull([Forms]![ReportsForm]![cmbArea]) Then
-
strCriteria=strCriteria & "(PunchListDetails.[Location/Area]) Like [Forms]![ReportsForm]![cmbArea] & '*'" & " AND "
-
End if
-
etc...
Finish with - strCriteria=Left(strCriteria, Len(strCriteria)-5)
to remove the last " AND "
Let me know if you get stuck
NeoPa 32,556
Expert Mod 16PB
I would recommend the same course :)
Add only the items that are used to the WHERE clause in the string.
Thank you both... this was driving me crazy, I can't belive you are not able to do it in the query itself, would be so much easier.
One last question once I have my nice new string strCriteria how do I pass this to a report. I have opened the report and gone to the vb code but I am not sure how to pass a filter to it on open? - Option Compare Database
-
-
Private Sub Report_Open(Cancel As Integer)
-
-
End Sub
Is the complete code for the command button (in case anyone is interested). - Private Sub cmdPrePunchDetails_Click()
-
On Error GoTo Err_cmdPrePunchDetails_Click
-
-
Dim strCriteria As String
-
strCriteria = ""
-
If Not IsNull([Forms]![ReportsForm]![cmbSystemCode]) Then
-
strCriteria = strCriteria & "PunchListDetails.[Associated System]) Like [Forms]![ReportsForm]![cmbSystemCode] & '*'" & " AND "
-
End If
-
If Not IsNull([Forms]![ReportsForm]![cmbArea]) Then
-
strCriteria = strCriteria & "(PunchListDetails.[Location/Area]) Like [Forms]![ReportsForm]![cmbArea] & '*'" & " AND "
-
End If
-
If Not IsNull([Forms]![ReportsForm]![cmbRaisedBy]) Then
-
strCriteria = strCriteria & "(PunchListDetails.[Entered By]) Like [Forms]![ReportsForm]![cmbRaisedBy] & '*'" & " AND "
-
End If
-
If Not IsNull([Forms]![ReportsForm]![cmbRaised]) Then
-
strCriteria = strCriteria & "(PunchListDetails.[Item Raised At]) Like [Forms]![ReportsForm]![cmbRaised] & '*'" & " AND "
-
End If
-
If Not IsNull([Forms]![ReportsForm]![cmbDiscipline]) Then
-
strCriteria = strCriteria & "(PunchListDetails.[Discipline]) Like [Forms]![ReportsForm]![cmbDiscipline] & '*'" & " AND "
-
End If
-
If Not IsNull([Forms]![ReportsForm]![cmbOwner]) Then
-
strCriteria = strCriteria & "(PunchListDetails.[Punch Owner]) Like [Forms]![ReportsForm]![cmbOwner] & '*'" & " AND "
-
End If
-
If Not IsNull([Forms]![ReportsForm]![cmbImpact]) Then
-
strCriteria = strCriteria & "(PunchListDetails.[Impact]) Like [Forms]![ReportsForm]![cmbImpact] & '*'" & " AND "
-
End If
-
If Not IsNull([Forms]![ReportsForm]![cmbContractor]) Then
-
strCriteria = strCriteria & "(PunchListDetails.[Responsible Contractor]) Like [Forms]![ReportsForm]![cmbContractor] & '*'" & " AND "
-
End If
-
If Not IsNull([Forms]![ReportsForm]![cmbItemType]) Then
-
strCriteria = strCriteria & "(PunchListDetails.[Item Type]) Like [Forms]![ReportsForm]![cmbItemType] & '*'" & " AND "
-
End If
-
-
strCriteria = Left(strCriteria, Len(strCriteria) - 5)
-
-
Dim stDocName As String
-
-
stDocName = "PunchListDetails"
-
DoCmd.OpenReport stDocName, acPreview
-
-
Exit_cmdPrePunchDetails_Click:
-
Exit Sub
-
-
Err_cmdPrePunchDetails_Click:
-
MsgBox Err.Description
-
Resume Exit_cmdPrePunchDetails_Click
-
-
End Sub
-
Dim stDocName As String
-
stDocName = "PunchListDetails"
-
DoCmd.OpenReport stDocName, acPreview, , strCriteria
That works perfectly.
Only exception is when no fields are entered, but I have a separate report for that.
Thank you very much.
NeoPa 32,556
Expert Mod 16PB
When no criteria are entered, you simply need to call it with : - DoCmd.OpenReport stDocName, acPreview
That is far easier then having report a and report b for updating.
Thanks again.
Damon
NeoPa 32,556
Expert Mod 16PB
No problem.
I'm pleased that worked for you and yes, it is easier ;)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: vishal |
last post by:
hi
here is vishal.
i am creating a site which will have one option for change language.
once user selects the language i am storing his reply in session
variable. then i want to display the...
|
by: JackT |
last post by:
I want to get a column count several times in one query using different
filters but can't work out how to do it - can anyone point me in the right
direction?
For example, how would combine these...
|
by: Rick Caborn |
last post by:
Does anyone know of a way to execute sql code from a dynamically built
text field?
Before beginning, let me state that I know this db architecture is
built solely for frustration and I hope to...
|
by: gregory.sharrow |
last post by:
I need to secure a datawarehouse table at the row level based on 1 to
many keys on that table. A user should only see the rows they have
access to. I need to be able to figure out which rows they...
|
by: chudson007 |
last post by:
I want to join 2 tables by a unique ID field, but the ID field also has
multiple NULLS which I do not want to ignore and I fear they will cause
duplication.
Using TableA and TableB below i will...
|
by: MP |
last post by:
Hi
trying to begin to learn database using vb6, ado/adox, mdb format, sql
(not using access...just mdb format via ado)
i need to group the values of multiple fields
- get their possible...
|
by: Ceebaby via AccessMonster.com |
last post by:
Hi All
Here's hoping someone can help me with this.
I have a report based on a query where the criteria for 4 of the fields is
set from an unbound form. I want the user to be able to select any...
|
by: Vivienne |
last post by:
Hi there
This is a hard problem that I have - I have only been using sql for a couple of weeks and have gone past my ability level quickly! The real tables are complex but I will post a simple...
|
by: javediq143 |
last post by:
Hi All,
This is my first post in this forum. I'm developing a CMS for my latest website. This CMS is also in PhP & MySQL. I'm done with the ADD section where the Admin can INSERT new records in...
|
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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
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: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |