Solved Nz date

[amir0914]

Hi everyone,
I wrote the below codes to filter subform by date and product code, this work correct, but I want to add nz to date for get all the dates.

querydate = "[CDate] =  #" & Me.txtDate & "# "

If Me.cmb01.ListIndex = 0 Then
pSQL = "select * from tblProducts where " & querydate & ""
ElseIf Me.cmb01.ListIndex = 1 Then
pSQL = "select * from tblProducts where " & querydate & " and Ccode = 6"
ElseIf Me.cmb01.ListIndex = 2 Then
pSQL = "select * from tblProducts where " & querydate & " Ccode = 4"
End if

Me.Child44.Form.RecordSource = pSQL

I added Nz function to querydate :

querydate = "[CDate] =  #" & nz(Me.txtDate,"*") & "# "

But it doesn't work and returns runtime error '3075'. can someone give me a way to ignore "querydate" or select all dates when it will be null?

 

[cheekybuddha]

Hi,

Do you really have a field in your table called 'CDate' ?

It is not a good name for a field because it is the name of a VBA function.

You can adjust your code like this to do what you need:

querydate = "([CDate] =  #" & Me.txtDate & "#" & IIf(IsNull(Me.txtDate), " OR True", vbNullString) & ") "
' ...

hth,

d

 

[cheekybuddha]

Actually, perhaps you will still get an error with that because of the empty date.

Try instead:

querydate = IIf(IsDate(Me.txtDate), "[CDate] =  #" & Me.txtDate & "# " , " 1 = 1 ")
' ...

hth,

d

 

[plog]

Simpler code is often easier to debug and expand and code with comments is always a necessity . The below code should (Honestly, I know me, and I probably made a syntax error somewhere) work to produce the SQL you want:

pSQL = "select * from tblProducts WHERE (1=1)"
  ' SQL statement that will become recordsource

querydate = ""
' Will hold CDate criteria if necessary

if IsDate(Me.txtDate) THEN querydate = " AND ([CDate] =  #" & Me.txtDate & "#)"
  ' if Me.txtDate is a date will use it in CDate criteria

If Me.cmb01.ListIndex = 0 Then pSQL = pSQL & querydate
If Me.cmb01.ListIndex = 1 Then pSQL = pSQL & querydate & " AND (Ccode = 6)"
If Me.cmb01.ListIndex = 2 Then pSQL - pSQL & querydate & " AND (Ccode = 4)"
' adds approriate criteria to SQL based on form inputs

Me.Child44.Form.RecordSource = pSQL

 

[amir0914]

Thanks to both of you, you're right CDate is not a correct name for the field name, I changed it to [EnterDate], I'm going to add another condition :

If Me.cmb01.ListIndex = 3 Then pSQL - pSQL & querydate & " AND (Ccode <> 4 or Ccode <> 6 )"

It means if ListIndex was 3 then select all the records without records that Ccode is 4 or 6. but it's not working. is that wrong?

 

[cheekybuddha]

Either:

If Me.cmb01.ListIndex = 3 Then pSQL - pSQL & querydate & " AND (Ccode <> 4 AND Ccode <> 6 )"

Or:

If Me.cmb01.ListIndex = 3 Then pSQL - pSQL & querydate & " AND Ccode NOT IN (4, 6)"

 

[cheekybuddha]

Just noticed a typo (from copy and paste).

Correct to:

If Me.cmb01.ListIndex = 3 Then pSQL = pSQL & querydate & " AND (Ccode <> 4 AND Ccode <> 6 )"

Or:

If Me.cmb01.ListIndex = 3 Then pSQL = pSQL & querydate & " AND Ccode NOT IN (4, 6)"

 

[amir0914]

Just noticed a typo (from copy and paste).

Correct to:

If Me.cmb01.ListIndex = 3 Then pSQL = pSQL & querydate & " AND (Ccode <> 4 AND Ccode <> 6 )"

Or:

If Me.cmb01.ListIndex = 3 Then pSQL = pSQL & querydate & " AND Ccode NOT IN (4, 6)"

I’m so grateful for your helps,

 

[cheekybuddha]

I'm glad we could help.