Dlookup with where clause

[Gazza2]

i hope this is in the right forum.

I have a subform with a textbox that is used for VAT(TxtVAT) and another textbox with a subtotal(TxtSubtotal).
I have a table with the different VAT rates and the dates From and To.

What i would like is on the OnChange event of the TxtSubtotal box i want to add a Dlookup to lookup the correct VatRate Dependent on the Taxdate on the main form.

I`m assuming i need a where clause of some sort but im not sure how to put that into the Dlookup statement

Any help would be much appreciated

Thanks
Gareth

 

[pbaldy]

This should help:

http://www.mvps.org/access/general/gen0018.htm

 

[Gazza2]

Thanks Paul

I had a look at this before and that is what has me confused as i want something like the following:

=Dlookup ("Vatrate","TblVat", where forms!FrmParent!Taxdate is inbetween the date from and date to in TblVat

 

[pbaldy]

Which would translate to:

FromDateField <= FormValue AND ToDateField >= FormValue

I'll let you translate that using the link as a guide.

 

[Gazza2]

still having trouble getting this to work. At the moment i am using the following code

Private Sub TxtSubTotal_AfterUpdate()
Dim vattotal As Integer
Dim Vatsum As Integer

vattotal = DLookup("VatRate", "TblVat", "DateFrom>= " & Forms!FrmInvoiceHeader!TaxDate & " AND DateTo <= '" & Forms!FrmInvoiceHeader!TaxDate & "'")

Vatsum = Me.TxtSubTotal + vattotal

End Sub

I have tried this in the onchange event also and this does not work.
I have tried it with the <> round the other way and this does not work.
I have tried it with a # in place of the ' and this does not work.

Any other pointers would be much appreciated

Thanks
Gareth

 

[pbaldy]

You missed in the link that date values must be surrounded by #. Try

vattotal = DLookup("VatRate", "TblVat", "DateFrom>= #" & Forms!FrmInvoiceHeader!TaxDate & "# AND DateTo <= #" & Forms!FrmInvoiceHeader!TaxDate & "#")

I think you've reversed the ">" and "<".

 

[Gazza2]

Thanks for the help Pbaldy the code was fine just needed a bit of fiddling with the way i had set it up on the form to get it to work.

Now i dont know if i should start a new post or carry this one on so here goes with the second part to my question.

I want to create a function with this so im thinking i would also need a second dlookup to lookup the documentNo(field in a table and on form) but i have no clue about functions.

Any help would be much appreciated.

 

[pbaldy]

Here are the very basics of creating a function:

http://www.baldyweb.com/Function.htm

Not sure what your exact requirements are, but it may be that opening a recordset on the appropriate SQL statement would be more efficient than multiple DLookup's.

 

[Gazza2]

Basically what i want is for this too be a function so when it is called by numerous forms it will call the correct VatValue.

I have had a go but cant get it to work correctly here is what i have so far

Public Function GetVatValue()
Dim VatAmount As Double

VatAmount = Nz(DLookup("VatRate", "TblVat", "DateFrom>= #" & TaxDate & "# AND DateTo <= #" & TaxDate & "#" And "DocumentNo = " & DocumentNo), 0)

End Function

I think i have the syntax right but i get a type mismatch error on the documentNo part which is a numerical value.

Thanks

 

[pbaldy]

Oh, you're so close. You have double quotes on either side of the last And that need to be deleted. I assume those are variables declared and set elsewhere? You also want to set the function name to the value to have it return it:

GetVatValue = Whatever

 

[Gazza2]

Ok i thought i understood the first part of your answer and changed it to the following:

Public Function GetVatValue()
Dim VatAmount As Double

VatAmount = Nz(DLookup("VatRate", "TblVat", "DateFrom>= #" & TaxDate & "# AND DateTo <= #" & TaxDate & "# And DocumentNo = " & DocumentNo), 0)

End Function

But i am now getting a syntax error (missing operator). Im not quite sure what i have done wrong.

As for the second half of your answer im totally baffled, not sure what you have assumed as variables as i thought the Dim VatAmount as Double was declaring a variable and not sure where else i was supposed to declare them.

As usual i jump in head first without a clue. I thought you just wrote the function and then put a call wherever you needed it.

Thanks for your help and patience so far
Gareth

 

[pbaldy]

The syntax looks okay offhand. My question dealt with the items in red:

VatAmount = Nz(DLookup("VatRate", "TblVat", "DateFrom>= #" & TaxDate & "# AND DateTo <= #" & TaxDate & "# And DocumentNo = " & DocumentNo), 0)

Where are those values coming from? If the code was on a form, I'd expect them to come from there, using Me or a full form reference. Otherwise, I'd expect them to be values passed to the function, as in my example. Also, you still aren't setting the name of the function to the value.

 

[Gazza2]

ok i think im getting you now.

The items that you have marked are fields in a table that will also be bound to different forms.

This is why i wanted a function so it pulls the correct vat value dependent on the date of the original document.

As for the value of the function, the vat value that i want it to find is stored in a table as a percentage so am i right in thinking that the first line should read

Public Function GetVatValue() as double

Thanks
Gareth

 

[pbaldy]

I guess the question is, how is the function to know which date and document to use? If you want it to find them on a specific form, you need to specify it with a full form reference. If you want this to be independent of any particular form, you need to change the function to accept parameters.

 

[Gazza2]

I do want this to be independent of any particular form.

Any pointers on the parameters part as im in unchartered waters with that.

Thanks
Gareth

 

[pbaldy]

Did you review the link I posted earlier? Where I have

strVariable As String

you'd want

dteTaxDate As Date

Similarly replacing the second argument with one for the document, with the appropriate data type. Then in your DLookup you'd use those 2 variables.