query problem

[virginie]

i want to unlock a label in a form according to the following condition:
i have a subform in the specific form with data linked to the form by the index key of the table.
in each line of the details form, i have a field "payment date"
in the first form i want to unlock field "real_total"
IF ALL the lines in the details have "payment date" .
so i check what is the count of lines with "payment" according to the id
and the total count of lines for this order

if they are equal, so i can unlock my field "total"
i try this:
******************
Dim stord As String

stord = Format(Me.id_order, "0000&quot

' SQLstr = "SELECT count(order_details.payment_date) as counter FROM order_details "
' SQLstr = SQLstr & "WHERE (((order_details.id_order)='"
' SQLstr = SQLstr & stord & "') AND ((order_details.payment_date) Is Not Null));"

' Set rs = CurrentDb.OpenRecordset(SQLstr)
' Dim payOrd As Integer
' payOrd = rs.Fields("counter&quot.Value


'SQLstr = "SELECT count(order_details.payment_date) as counter FROM order_details "
' SQLstr = SQLstr & "WHERE order_details.id_order='" & stord & "';"

'Set rs = CurrentDb.OpenRecordset(SQLstr)
' Dim allOrd As Integer
' allOrd = rs.Fields("counter&quot.Value

' If allOrd = payOrd Then
' real_total.Locked = False
' End If
*************

but if for one id that dont have "payment date" at all it makes error on:
'Set rs = CurrentDb.OpenRecordset(SQLstr)

if there are like this, it pass this line
but error msg "data type mistach in criteria expression"

what is wrong there????


__________________
viv

 

[Darrylles]

Hi,

Not clear about your explanation, but, look at 'NZ' function.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[RobbieMac]

This is how I would tackle your problem
Some of this may seem obvious sorry !

Dim stord As String

' MAC Change 1
Dim SQLstr As String
Dim rs As Recordset
Dim dbs As Database
Set dbs = CurrentDb() ' This is just good form
' End MAC Change 1

real_total.Locked = True
real_total.Enabled = False

stord = Format(Me.id_order, "0000&quot 'Why you do you do this ?

' Simplify your first statement to get a true value of these orders NOT A COUNT !!!
SQLstr = "SELECT * FROM [order_details] WHERE [id_order]='" & stord & " ' AND [payment_date] Is Not Null"

Set rs = dbs.OpenRecordset(SQLstr)

Dim payOrd ' don't specify type here let access return the value it has
payOrd = rs.RecordCount ' change the query counter here to the value of the records returned by the Select statement

If payOrd = Null Then payOrd = 0 ' then mutate the value if null to a zero
' then any calculation you may subsequently perform on this
' value will be sure to function.

' RESET your recorset FIRST
Set rs = Nothing

SQLstr = "SELECT * FROM [order_details] WHERE [id_order]='" & stord & "'"

Set rs = dbs.OpenRecordset(SQLstr)

Dim allOrd ' don't specify type here let access return the value it has
allOrd = rs.RecordCount

If allOrd = Null Then allOrd = 0

'This only makes sense if you have a control or field ; a Tip ; LOCK it ! set property .enabled to true and vice versa as below
' Don't quite know what you want here but you hopefully do !
If allOrd = payOrd Then
real_total.Locked = False
real_total.Enabled = True
End If

*************