create field in query (calculated) using using vba 1

[boomer1974]

Hi All,

I have a query that I have built a subquery off of, my problem is that I need to create a calculated field in my main query but It requires looping, if statements, etc. so I would like to create or update this new field in code. I don't think that I can just write the sql in code because I still need the query as the source for the subquery. Is it possible to do what I want using qrydefs and recordsets? If anyone has an example it would be greately appreciated.

Thanks,
Ryan

 

[dhookom]

I don't think you have provided enough information. Do you understand that you can write a function that can be used as the source for a column in a query?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[boomer1974]

I did not realize you could do that, how would you execute or call it from the query?

 

[dhookom]

Assuming a function that just averages two numbers:
Create a new module and enter:
Public Function AverageTwo(pdblA As Double, _
pdblB as Double) as Double
AverageTwo = (pdblA + pdblB)/2
End Function

If you have two numeric fields ([FldA] and [FldB]) in a query and want to average them, create a column in the query:
TheAverage:AverageTwo([FldA], [FldB])

Of course, functions can get much more complex. You can generally use a user-defined function in all the same places you would use an Access function.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[boomer1974]

Maybe I should clarify what I need to do. I have a user defined field in a query "LoadingTimeInMinutes: Int(DateDiff("n",[traileropened],Now())" which give me the total time a trailer has been in the process of loading - in minutes - this process could span several days. I now need to subrtact from that the time that there was no activity in the building (which is on Fri, Sat, and Sun nights after 6:30 PM - I know how to determine how many weekend nights there are between two dates but only by using vba, I need to have it in a query that can be used as the source for a subquery, can this be done???

 

[dhookom]

Seems I have seen this question elsewhere... You need to figure out the logic and code it in the function like:

Your function would need to look something like:
Function LoadingTime(pdatStart as Date) as Double
Dim intWeeks as Integer
Dim intReturnMinutes as Integer
Dim intMinsToRemove as Integer
Dim intSubMinsInWeekend as Integer 'remove how many minutes
intSubMinsInWeekend = 16 * 60
intReturnMinutes = Int(DateDiff("n",pdatStart,Now())
intMinsToRemove = ((Date -pdatSart)\7) * intSubMinsInWeekend
'we have removed minutes from total week - weekends
'you need to figure out and remove the remainder
LoadingTime = intReturnMinutes - intMinsToRemove
End Function


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[boomer1974]

Duane,

Thanks so much for that function it is much more elegant that the one I had written, the only thing that I still do not understand is how to have my query execute your function to populate a field when I run the query. Again, thanks so much for bearing with me on this. I'm learning!!!

 

[lespaul]

In your query you would write:

SELECT LoadingTime(DateFieldInTable) From TblName

Leslie

 

[dhookom]

As stated by Leslie create a new column in the grid:
LoadTime: LoadingTime([traileropened])
This assumes your TrailerOpened field will always have date value and never be null in the query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dhookom]

I should also mention that I left out lots of code to remove additional minutes for partial weeks and days.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[boomer1974]

can this be done using the access query grid and if the source is another query?

 

[boomer1974]

please excuse my last post you answered so quickly and I posted after your answer. star for you thanks a million. I learned many new tricks today.