Hi!!

[shikha77]

Hi!!
I am a new member here .... i have a vb code :

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Fees Maintenance", DB_OPEN_DYNASET)
If Me.Invoice_type = "company invoice" Then
criterion = "Company_code = " & Me.Text4 & " And fromdate= #" & Me.Text0 & "#"
Else
criterion = "Memnum = " & Me.Text4
End If

Now the problem is that even if a date exists in fee maintenance table which is same as Me.Text0 (and code also matches Text4 control)then also it does not matches the criteria. why is that happening?? Otherwise if i remove the date part ie " And fromdate= #" & Me.Text0 & "#" from this line :
criterion = "Company_code = " & Me.Text4 & " And fromdate= #" & Me.Text0 & "#"
then it matches. I think there is some other syntax for comparing dates ..what is it??

 

[RuralGuy]

The best way I've found to learn SQL syntax is to use the Query Builder and examine the SQL behind the grid.

 

[shikha77]

Hi!

but this si not sql code ..for more clarity i will give more code :
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Fees Maintenance", DB_OPEN_DYNASET)

If Me.Invoice_type = "company invoice" Then
criterion = "Company_code = " & Me.Text4 & " And fromdate= #" & Me.Text0 & "#"
Else
criterion = "Memnum = " & Me.Text4
End If

If rst.BOF Then
Exit Function
Else
rst.MoveFirst
rst.FindLast criterion
MsgBox criterion
If rst.NoMatch = False Then
MsgBox "gone in if loop"
rst.Edit
rst!invoice_no = InvNo
rst.Update

Else
MsgBox "not gone in if loop"
End If

End If
rst.Close

 

[Uncle Gizmo]

I notice that you have added some message boxes to return indications of how your code is working. So you obviously have some experience in debugging your own code.

All I was going to suggest is that I find it is a good idea to remove variables, and replace them with hard coded "variables" (for want of a better word) This way you know exactly what the code should do, you are not dependent on the actual variables being correct or not. This will help you avoid issues like whether the date is being interpreted as UK or U.S.. Once you have got it working with a hard coded variable, it's only a case of getting the dates in the right format.

 

[RuralGuy]

I'm sorry but the FindFirst *is* SQL:

criterion = "[Company_code] = " & Me.Text4 & " AND [fromdate] = #" & Me.Text0 & "#"

...if [Company_code] is numeric or:

criterion = "[Company_code] = """ & Me.Text4 & """ AND [fromdate] = #" & Me.Text0 & "#"

...if [Company_code] is a text field.

It is comperable to the WHERE clause in SQL!