Query Formulae as VB code

[khwaja]

I have to use a lot of variations to figure out delivery required dates. I can have only limited number of formulae in the query. Is there a way to put all these conditions in vb and apply the result into a query. Following is an example but these could be up to ten formulae with changes in division, development code etc.

ReqDate3: IIf([Division] Like "*FSL*" And [Devcode] Like "*N*" And [Traderscomp] Is Not Null,DateAdd("d",-77,[builderComp]),IIf([Division] Like "*FSL*" And [Devcode] Like "*N*" And IsNull([Traderscomp]),DateAdd("d",-84,[BuilderComp]),Null))

Will appreciate some help.

 

[pbaldy]

You can create a public function that returns the value based on the inputs. Here's a primer:

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

In your query, it would look like:

ReqDate3: FunctionName([Division], [Devcode], etc)

 

[khwaja]

Thanks a lot. I looked at the primer. As it does not quite show any example code, I was wondering if you could help me create just a line of code and I can then replicate the same. Do I need to declare any variable?

Cheers

 

[pbaldy]

Not knowing your process, I don't know, but I doubt it. Your variables would be the input parameters of the function:

Public Function ReturnReqDate(strDivision As String, etc) As Date

Based on what you posted, you'll need either If/Then/Else or Select/Case, or maybe both. More info on both in VBA help.

If strDivision Like "*FSL*" And [Devcode] Like "*N*" And Not IsNull([Traderscomp]) Then
  ReturnReqDate = DateAdd("d",-77,[builderComp])

Replacing with the appropriate input parameters as I did for the first.

 

[khwaja]

Much appreciate. Will give it a go. Thanks for guidance.

 

[pbaldy]

No problemo; post back if you get stuck.

 

[khwaja]

Sorry to bother you again. I have used the following function but it gives me a type mismatch error. I am not too sure why do I have to declare Division as string as I have other criteria too. Therefore, I removed it from the function name. May be I am wrong.

Public Function ReturnReqDate() As Date

If [Division] Like "*FSL*" And [DevCode] Like "*N*" And Not IsNull([TradersComp]) Then
ReturnReqDate = DateAdd("d", -77, [BuilderComp])

End If
End Function

 

[khwaja]

I improved the code little bit more but now I get 12:00:00 AM as the retun value.

Public Function ReturnReqDate() As Date

On Error GoTo Err_ReturnReqDate

Dim strDivision As String, strDevCode As String, dteTradersComp As Date, dteBuilderComp As Date

If strDivision Like "*FSL*" And strDevCode Like "*N*" And Not IsNull(dteTradersComp) Then
ReturnReqDate = DateAdd("d", -77, dteBuilderComp)
End If

Exit_ReturnReqDate:
Exit Function
Err_ReturnReqDate:
MsgBox "Error " & err.Number & " " & err.Description
Resume Exit_ReturnReqDate
End Function

 

[pbaldy]

Of course; you're declaring variables instead of using the input parameters as I recommended. The variable is never given a value, so its value is zero. Zero as a date/time value is 12am.

 

[khwaja]

Thanks. I changed it as under but am I expected to supply the arguments in the brackets if I were to test it. I was merely using this function in a query to work out the right date by testing criteria based on the function.

Public Function ReturnReqDate(strDivision As String, strDevCode As String, dteTradersComp As Date, dteBuilderComp As Date) As Date

On Error GoTo Err_ReturnReqDate


If strDivision Like "*FSL*" And strDevCode Like "*N*" And Not IsNull(dteTradersComp) Then
ReturnReqDate = DateAdd("d", -77, dteBuilderComp)
End If

Exit_ReturnReqDate:
Exit Function
Err_ReturnReqDate:
MsgBox "Error " & err.Number & " " & err.Description
Resume Exit_ReturnReqDate
End Function

 

[pbaldy]

I was assuming that you would want to use other fields from the query to figure out the date to return. Presuming so, you would call it as I demonstrated in post 2.

 

[khwaja]

Thanks I tried to call it but my query does not accept parenthesis in my expresion. Is there a particular format for calling a function other than this? Test:ReturnReqDate

 

[pbaldy]

I just tested this successfully:

Test: CheckAbbr([ConLastName])

Can you post the db?

 

[khwaja]

Thanks. I have attached the db. Basically, I would like to run a query to show me the required date using a function.

Cheers

AK

 

[pbaldy]

You had brackets around the function name, which made Access think it was a field (it may have added them on you). This runs without error:

Test: Returnreqdate([Division],[ProjectCategory],[TradersComp],[ReqDate])

I of course may have picked the wrong fields. They didn't all make sense to me. You will also want to have an Else clause to handle what the function should return when the If clause is not met.

 

[khwaja]

Thanks a lot. When I ran with amended syntax, I got 12:00:00 AM in all cases. Did you get the same when you ran? I have uploaded the db again which has the correct fields in it. I am sorry I did not pay much attention to the contents of query when creating this emp db.

I will definitely add else clauase.

 

[pbaldy]

I got 12:00:00 AM in all cases. Did you get the same when you ran?

They appear to at first, but if I put similar criterion in the query as the If clause (so the query is only returning records that would match that test), there are what appear to be valid dates. See screen shot (note the errors when there's no BuilderComp date, which obviously needs to be accounted for in your function).

 

[khwaja]

Thank you kindly. Yes I could successfully run an iif expression in the query but there were so many variants that Access could not handle all statements. Is there a way we can fix the expression in the query so that a function can be used?

 

[pbaldy]

The picture I posted was the result of your function. All I did was to restrict the records returned so I could tell if the function was working. When you first looked at it and saw all the 12am records, it was because none of the records matched the test in the If portion of the function. The query normally returns 709 records. Only 41 of those have values that the function will return a date for.

 

[khwaja]

Understand. Many thanks. I should be Ok to build the rest of the function. Appreciate your patience.