Dcount By Multiple Textbox

[civl_eng]

hi guys, i want to use Dcount for count by multiple text box but when one textbox is null doesn't find any record, and i have to use alot of if and elseif for this purpose, for example i wrote Multiple elseif tow textbox :




If Not IsNull(Me.Text4) And Not IsNull(Me.Text6) Then


x = DCount("ID", "tbl_Patients", "[P_Sex]= '" & Me.Text4 & "' and [P_Status]= '" & Me.Text6 & "'")


ElseIf Not IsNull(Me.Text4) And IsNull(Me.Text6) Then


x = DCount("ID", "tbl_Patients", "[P_Sex]= '" & Me.Text4 & "'")


ElseIf IsNull(Me.Text4) And Not IsNull(Me.Text6) Then




x = DCount("ID", "tbl_Patients", "[P_Status]= '" & Me.Text6 & "'")


ElseIf IsNull(Me.Text4) And IsNull(Me.Text6) Then


x = DCount("ID", "tbl_Patients")


End If


MsgBox x

,,,,,,,,,,
for this problem i try to use Nz function for doesn't consider null textbox as below code :

x = DCount("ID", "tbl_Patients", "[P_Sex]= '" & Nz(Me.Text4, "") & "' and [P_Status]= '" & Nz(Me.Text6, "") & "'")

but it's not working again


is there any better solution?? (i have to do this without querry)

thanks in advanced and sorry for my poor english

[June7]

Maybe use LIKE and wildcard.

x = DCount("ID", "tbl_Patients", "[P_Sex] LIKE '" & Nz(Me.Text4, "*") & "' AND [P_Status] LIKE '" & Nz(Me.Text6, "*") & "'")

[pbaldy]

A shot in the dark:

x = DCount("ID", "tbl_Patients", "[P_Sex] Like '" & Nz(Me.Text4, "*") & "' and [P_Status] Like '" & Nz(Me.Text6, "*") & "'")

[pbaldy]

Oops, sorry June. I guess great minds think alike...sometimes mine too.

[civl_eng]

hey,,,thank you so much june7 and pbaldy,
Now i have another problem, i want to add tow Dates range for this code as same your code and if Text boxes of date are empty doesn't consider them in querry, i wrote this code for date querry : (Type of date is Text)



x = DCount("ID", "tbl_Patients", "[P_Date] Between '" & Me.Text0 & "' And '" & Me.Text2 & "'")

And i combined the code(tow dates) with th first code(gender,marital status)

x = DCount("ID", "tbl_Patients", "[P_Sex] LIKE '" & Nz(Me.Text4, "*") & "' AND [P_Status] LIKE '" & Nz(Me.Text6, "*") & "' And [P_Date] Between '" & Me.Text0 & "' And '" & Me.Text2 & "'")

Above code is working if all of the textbox is fill, but when textboxes of Date is empty doesn't find any record, is it possible to use Like,NZ in Textboxes of Date???

Thanks again for the first help.Today, 06:19 AM
pbaldy
Today, 06:19 AM
pbaldy

[pbaldy]

The Nz() function, with dates before/after anything in your data.

[pbaldy]

Oh, and dates need # as a delimiter, not '.

[civl_eng]

can you give me the corrected of code ?? (type of date is Text)

[pbaldy]

Like

Nz(Me.Text0, #1/1/1980#)

I would question the date field being text. You'll probably have wonky issues with it, can't do date math, etc.

[June7]

If date is stored in a text type field, code needs to convert to date type for the criteria to work. Try CDate() function.

Why would date be in a text field?

[civl_eng]

hey, thank you all, sorry for the delay in replying.

my date is persian date and access doesn't support it, and we have to use text type instead date fields.

[June7]

What is Persian date - international structure? http://allenbrowne.com/ser-36.html

[civl_eng]

can you add "Like" and "nz" for textboxes in this below code ????

x = DCount("ID", "tbl_Patients", "[P_Date] Between '" & Me.Text0 & "' And '" & Me.Text2 & "'")

[June7]

LIKE and wildcard is for text data not numbers. As already advised, dates as text will not work for chronological sequencing or calculating. True date values are actually numbers with a specific display format.

When stored as text 3/25/2018 will not sort before 10/25/2018, however 03/25/2018 will because this is alpha sorting.