data type mismatch in criteria expression and invalid use of null.

[samuel84]

Hi,

I am getting the error "invalid use of null" when summing value from a range which has no value and "data type mismatch in criteria expression" when there are values to be summed up. Is there an error in my code? could someone please point out the error and why am I gettin these errors.

Private Sub cmdCalculate_Click()

Dim uDT1 As String
Dim uDT2 As String
Dim uGT As Double

uDT1 = Format(DTPicker1.Value, "dd/mm/yyyy")
uDT2 = Format(DTPicker2.Value, "dd/mm/yyyy")
uGT = Val(txtGT)

rs.Open "SELECT Sum(AmountP) FROM tblMC where [Date] BETWEEN #" & uDT1 & "# and #" & uDT2 & "#", conn, adOpenKeyset, adLockOptimistic, adCmdText
uGT = rs.Fields(0)
rs.Close

End Sub

TQ.

[lotus18]

Hi,

I am getting the error "invalid use of null" when summing value from a range which has no value and "data type mismatch in criteria expression" when there are values to be summed up. Is there an error in my code? could someone please point out the error and why am I gettin these errors.
[code=vb]
Private Sub cmdCalculate_Cl ick()

Dim uDT1 As String
Dim uDT2 As String
Dim uGT As Double

uDT1 = Format(DTPicker 1.Value, "dd/mm/yyyy")
uDT2 = Format(DTPicker 2.Value, "dd/mm/yyyy")
uGT = Val(txtGT)

rs.Open "SELECT Sum(AmountP) FROM tblMC where [Date] BETWEEN #" & uDT1 & "# and #" & uDT2 & "#", conn, adOpenKeyset, adLockOptimisti c, adCmdText
uGT = rs.Fields(0)
rs.Close

End Sub
[/code]

TQ.

You will get an error if the record is null (rs.Fields(0). Adding An On Error Resume Next below the sql statement may fix this. and also check what datatype were you using in your table it should be matched with your datatype declaration in your vb.

Rey Sean

[samuel84]

You will get an error if the record is null (rs.Fields(0). Adding An On Error Resume Next below the sql statement may fix this. and also check what datatype were you using in your table it should be matched with your datatype declaration in your vb.

Rey Sean

I tried what you suggested there, and yes no error appears, but is it possible for it to retun "0" if the sum equals to 0?is there any way?

Initially in the DB the column Date datatype was text, but now i changed it to Date/Time but still error persist. Any ideas?

[Killer42]

Could you interrupt execution (use a breakpoint) just before executing the Open, print out the actual string that is being passed, and paste it here?

In other words, at the point where it is just about to execute the rs.Open, copy and paste to the Immediate window and issue this command...
[CODE=vb]Print "SELECT Sum(AmountP) FROM tblMC where [Date] BETWEEN #" & uDT1 & "# and #" & uDT2 & "#"[/CODE]

[Killer42]

Oops! I think I see the problem.

Use mm/dd/yyyy (American) format in those date strings. Access insists on them being that way. (Actually, I think it might be an SQL standard, rather than Access.)

[samuel84]

Oops! I think I see the problem.

Use mm/dd/yyyy (American) format in those date strings. Access insists on them being that way. (Actually, I think it might be an SQL standard, rather than Access.)

in my db..i saved the date as dd/mm/yyyy which the column to be searched.i saw it elsewhere that since the dtpicker is just a string so it can be chosen as any type as it is not following the system clock.

ok,before goin any further, is my code correct?
my intention for its job to be done is as follows:
check the db based on the dtpicker's date(2 different dates or same) then check the corresponding values in the AmountP column and sum up the values based on the range of the dtpicker's and then display the sum at the textbox "uGT".

[Killer42]

It's important to remember that dates aren't stored in the database in any particular "date format". It's just a number. But to talk to the SQL interpreter, you need to use a string that it can recognise and interpret. And being written in the States, it uses US format for this. Note that this won't always show up, as I think it's smart enough to work out unambiguous dates. For example, "25/03/2008" only works one way, so probably won't cause problems. But "01/02/2003" could be either 1st of Feb or 2nd of Jan. To the SQL interpreter, it's January.

I think your code looks fine, apart from the date format as mentioned. I haven't used Sum() function in my SQL, so can't guarantee anything. Personally I normally use the Access query designer to build a query, then copy and paste the SQL.

For a full discussion on this topic, have a look at the index of articles posted in the Access forum.