Fear Naught
Kevin
- Local time
- Today, 14:41
- Joined
- Mar 2, 2006
- Messages
- 229
Hi folks.
I have tried to find the answer in these forums to no avail.
I have a database that staff use to record their business journies, fuel bought etc, etc. On the form for inputting fuel bought users have to input the mileage on the car when they re-fuelled. What I want to show is the mileage recorded on the previous fill so that I can calculate fuel consumption.
I have a query that does that but I need to be able to run that query in a function to return the value to a text box on my form. I have tried what is my usual method, that is to say running the SQL in VBA but I get a NULL result. As I know the stored query works I tried that but got and error saying "expected 1"
The code for both examples is below
Code for embedded SQL
Code for trying to run a stored query
I do not get syntax errors just no data. If I run the query outside of the code I get the required result.
HELP
EDIT: Yes I know that I have a reserved name as the "date" field in my table. I will work to get rid of that in due course.
I have tried to find the answer in these forums to no avail.
I have a database that staff use to record their business journies, fuel bought etc, etc. On the form for inputting fuel bought users have to input the mileage on the car when they re-fuelled. What I want to show is the mileage recorded on the previous fill so that I can calculate fuel consumption.
I have a query that does that but I need to be able to run that query in a function to return the value to a text box on my form. I have tried what is my usual method, that is to say running the SQL in VBA but I get a NULL result. As I know the stored query works I tried that but got and error saying "expected 1"
The code for both examples is below
Code for embedded SQL
Code:
Public Function funcPrevMile()
Dim strSQL As String
Dim Rs As DAO.Recordset
strSQL = "select max(mileage) as mMile from tblfuel F where f.date < " & Forms!frmFuel!Date & " and f.usernameid = getuserid() and f.vehicleid = getvehicleid()"
Set Rs = CurrentDb.OpenRecordset(strSQL)
funcPrevMile = Rs!mMile
Rs.Close
Set Rs = Nothing
End Function
Code for trying to run a stored query
Code:
Public Function funcPrevMile()
Dim qryMax As DAO.QueryDef
Dim Rs As DAO.Recordset
Set qryMax = CurrentDb.QueryDefs("qryMaxMiles")
Set Rs = qryMax.OpenRecordset
funcPrevMile = Rs!mMile
Rs.Close
Set Rs = Nothing
End Function
I do not get syntax errors just no data. If I run the query outside of the code I get the required result.
HELP
EDIT: Yes I know that I have a reserved name as the "date" field in my table. I will work to get rid of that in due course.
Last edited: