open query based on many choices

basilyos

Registered User.
Local time
Today, 04:21
Joined
Jan 13, 2014
Messages
256
hello i wrote this code to open a report based on a query
and this query is based on the toggle button

the problem is if i press one toggle button all work
but if i press more than toggle button in the same time it will not give me the right records or it will give me an empty report

this is the code

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tbl_Mouzakarat"

Dim p_7abes As String
Dim p_gharame As String
Dim p_done As String
Dim p_undone As String
Dim p_khoulasa As String
Dim p_mouzakara As String
Dim p_karar As String
Dim p_jaze2e As String
Dim p_lebanese As String
Dim p_foreign As String
Dim p_SQL_criteria As String

p_7abes = Trim(Me!text_7abes & " ")
p_gharame = Trim(Me!text_gharame & " ")
p_done = Trim(Me!text_done & " ")
p_undone = Trim(Me!text_undone & " ")
p_khoulasa = Trim(Me!text_khoulasa & " ")
p_mouzakara = Trim(Me!text_mouzakara & " ")
p_karar = Trim(Me!text_karar & " ")
p_jaze2e = Trim(Me!text_jaze2e & " ")
p_lebanese = Trim(Me!text_lebanese & " ")
p_foreign = Trim(Me!text_lebanese & " ")

If p_7abes <> "" Then
p_SQL_criteria = "[Punish]" & " LIKE '" & p_7abes & "'"
End If
If p_gharame <> "" Then
p_SQL_criteria = "[Punish]" & " LIKE '*" & p_gharame & "*'"
End If
If p_done <> "" Then
p_SQL_criteria = "[Status_Check]" & " LIKE '*" & p_done & "*'"
End If
If p_undone <> "" Then
p_SQL_criteria = "[Status_Check]" & " LIKE '*" & p_undone & "*'"
End If
If p_khoulasa <> "" Then
p_SQL_criteria = "[Type]" & " LIKE '*" & p_khoulasa & "*'"
End If
If p_mouzakara <> "" Then
p_SQL_criteria = "[Type]" & " LIKE '*" & p_mouzakara & "*'"
End If
If p_karar <> "" Then
p_SQL_criteria = "[Type]" & " LIKE '*" & p_karar & "*'"
End If
If p_jaze2e <> "" Then
p_SQL_criteria = "[Type]" & " LIKE '*" & p_jaze2e & "*'"
End If
If p_lebanese <> "" Then
p_SQL_criteria = "[Nationality]" & " LIKE '*" & p_lebanese & "*'"
End If
If p_foreign <> "" Then
p_SQL_criteria = "[Nationality]" & " NOT LIKE '*" & p_foreign & "*'"
End If

If Me.chk7abes.Value = True Then
DoCmd.RunSQL "INSERT INTO tbl_Mouzakarat select * from " & "[qry_Mouzakarat]" & " where " & p_SQL_criteria
End If
If Me.chkGharame.Value = True Then
DoCmd.RunSQL "INSERT INTO tbl_Mouzakarat select * from " & "[qry_Mouzakarat]" & " where " & p_SQL_criteria
End If
If Me.chkDone.Value = True Then
DoCmd.RunSQL "INSERT INTO tbl_Mouzakarat select * from " & "[qry_Mouzakarat]" & " where " & p_SQL_criteria
End If
If Me.ChkUndone.Value = True Then
DoCmd.RunSQL "INSERT INTO tbl_Mouzakarat select * from " & "[qry_Mouzakarat]" & " where " & p_SQL_criteria
End If
If Me.chkKhoulasa.Value = True Then
DoCmd.RunSQL "INSERT INTO tbl_Mouzakarat select * from " & "[qry_Mouzakarat]" & " where " & p_SQL_criteria
End If
If Me.chkMouzakara.Value = True Then
DoCmd.RunSQL "INSERT INTO tbl_Mouzakarat select * from " & "[qry_Mouzakarat]" & " where " & p_SQL_criteria
End If
If Me.chkKarar7abes.Value = True Then
DoCmd.RunSQL "INSERT INTO tbl_Mouzakarat select * from " & "[qry_Mouzakarat]" & " where " & p_SQL_criteria
End If
If Me.chkKararJaze2e.Value = True Then
DoCmd.RunSQL "INSERT INTO tbl_Mouzakarat select * from " & "[qry_Mouzakarat]" & " where " & p_SQL_criteria
End If
DoCmd.OpenReport "rpt_Mouzakarat", acViewPreview
DoCmd.Close acForm, "frm_Printing"


will someone check the code please
 
Please use code tags when posting code, click the # button in the post menu or type
[cade] code here [/cade] replacing a by o, offcourse.


Some semi random observations
Why do you append the data to a (new) table? why not " simply " run a query to report off?

Not all your like statements have wildcards, is that intended?
Are all your fields indeed text fields?
Why do you do the trim and append the space? Do you know of the NZ function?
Please indent your code to make it a bit more managable long term.
Why repeat your query statements several times? Maintenance wize should it ever need to happen its much better to only have the (major part of) SQL in a single place.
 
hey namiliam am new to access so will you help me to correct the code
i don't know what nz do
all my fields are text fields
no answer for other questions

please help and if there is other solution

thanks in advance
 
cant help you more without more answers, or a sample of your database with an explained example of what you are getting and what you are expecting to get and why....
 
You're off to a good start for a beginner, but you have a few issues, one of which is that you were overwriting your criteria with each new criterion. Another biggie is that you're using multiple controls to define criteria for the same field, which requires separate handling to be done properly and use OR when appropriate. Also, just refer to Me.Control to get the value, not Me.Control.Value, which may cause problems with unbound fields. I think your appending spaces to the end of the tested values was an attempt to ensure an empty string to test against, so I've corrected that by removing the space.

Here is some untested air code that may get you on the path to what you need. This could be tidied up and benefit from a Select Case structure, but as that may be more confusing, I've stuck with If.

Code:
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tbl_Mouzakarat"

Dim p_7abes As String
Dim p_gharame As String
Dim p_done As String
Dim p_undone As String
Dim p_khoulasa As String
Dim p_mouzakara As String
Dim p_karar As String
Dim p_jaze2e As String
Dim p_lebanese As String
Dim p_foreign As String
Dim p_SQL_criteria As String
Dim strSQL As String

p_7abes = Trim(Me!text_7abes) & ""
p_gharame = Trim(Me!text_gharame) & ""
p_done = Trim(Me!text_done) & ""
p_undone = Trim(Me!text_undone) & ""
p_khoulasa = Trim(Me!text_khoulasa) & ""
p_mouzakara = Trim(Me!text_mouzakara) & ""
p_karar = Trim(Me!text_karar) & ""
p_jaze2e = Trim(Me!text_jaze2e) & ""
p_lebanese = Trim(Me!text_lebanese) & ""
p_foreign = Trim(Me!text_lebanese) & ""
p_SQL_criteria = ""

If p_7abes <> "" Then
	If p_SQL_criteria="" Then
		p_SQL_criteria = "[Punish]" & " LIKE '" & p_7abes & "'"
	Else
		p_SQL_criteria = p_SQL_criteria & " AND [Punish]" & " LIKE '" & p_7abes & "'"
	End If
ElseIf p_gharame <> "" Then
	If p_SQL_criteria="" Then
		p_SQL_criteria = "[Punish]" & " LIKE '*" & p_gharame & "*'"
	Else
		p_SQL_criteria = p_SQL_criteria & " AND [Punish]" & " LIKE '*" & p_gharame & "*'"
	End If
End If
If p_done <> "" Then
	If p_SQL_criteria="" Then
		p_SQL_criteria = "[Status_Check]" & " LIKE '*" & p_done & "*'"
	Else
		p_SQL_criteria = p_SQL_criteria & " AND [Status_Check]" & " LIKE '*" & p_done & "*'"
	End If
ElseIf p_undone <> "" Then
	If p_SQL_criteria="" Then
		p_SQL_criteria = "[Status_Check]" & " LIKE '*" & p_undone & "*'"
	Else
		p_SQL_criteria = p_SQL_criteria & " AND [Status_Check]" & " LIKE '*" & p_undone & "*'"
	End If
End If
If p_khoulasa <> "" Then
	If p_SQL_criteria="" Then
		p_SQL_criteria = "[Type]" & " LIKE '*" & p_khoulasa & "*'"
	Else
		p_SQL_criteria = p_SQL_criteria & " AND [Type]" & " LIKE '*" & p_khoulasa & "*'"
	End If
ElseIf p_mouzakara <> "" Then
	If p_SQL_criteria="" Then
		p_SQL_criteria = "[Type]" & " LIKE '*" & p_mouzakara & "*'"
	Else
		p_SQL_criteria = p_SQL_criteria & " AND [Type]" & " LIKE '*" & p_mouzakara & "*'"
	End If
ElseIf p_karar <> "" Then
	If p_SQL_criteria="" Then
		p_SQL_criteria = "[Type]" & " LIKE '*" & p_karar & "*'"
	Else
		p_SQL_criteria = p_SQL_criteria & " AND [Type]" & " LIKE '*" & p_karar & "*'"
	End If
ElseIf p_jaze2e <> "" Then
	If p_SQL_criteria="" Then
		p_SQL_criteria = "[Type]" & " LIKE '*" & p_jaze2e & "*'"
	Else
		p_SQL_criteria = p_SQL_criteria & " AND [Type]" & " LIKE '*" & p_jaze2e & "*'"
	End If
End If
If p_lebanese <> "" Then
	If p_SQL_criteria="" Then
		p_SQL_criteria = "[Nationality]" & " LIKE '*" & p_lebanese & "*'"
	Else
		p_SQL_criteria = p_SQL_criteria & " AND [Nationality]" & " LIKE '*" & p_lebanese & "*'"
	End If
ElseIf p_foreign <> "" Then
	If p_SQL_criteria="" Then
		p_SQL_criteria = "[Nationality]" & " NOT LIKE '*" & p_foreign & "*'"
	Else
		p_SQL_criteria = p_SQL_criteria & " AND [Nationality]" & " NOT LIKE '*" & p_foreign & "*'"
	End If
End If

strSQL = "INSERT INTO tbl_Mouzakarat select * from qry_Mouzakarat Where " & p_SQL_criteria

If Me.chk7abes = True or Me.chkGharame = True or Me.chkDone = True or Me.ChkUndone = True or Me.chkKhoulasa = True or Me.chkMouzakara = True or Me.chkKarar7abes = True or Me.chkKararJaze2e = True Then
DoCmd.RunSQL strSQL
End If

DoCmd.OpenReport "rpt_Mouzakarat", acViewPreview
DoCmd.Close acForm, "frm_Printing"
 

Users who are viewing this thread

Back
Top Bottom