query issue

[crujazz]

Hi folks,

by using this code i'll get in my query the answer 4 times.... what am i doing wrong?

Function GDT() As String

Dim db As Database, Lrs As DAO.Recordset, LSQL As String, LT As String

Set db = CurrentDb()
Set Lrs = db.OpenRecordset(LSQL, dbOpenDynaset)

LSQL = "select date from Test where date=Date()"

If Not Lrs.EOF = True Then
LT = Lrs.Fields("date").Value
Else

LT = "Not Found"
End If

Lrs.Close

Set Lrs = Nothing
Set db = Nothing

GDT = LT

End Function


Thnx

 

[GUIDO22]

Perhaps you can post a copy of your calling query ? - from what you suggest it appears that your query is calling the function 4 times.

You will need to define the LSQL string BEFORE opening the recordset though!

Also the Date function will need to be defined outside of the quotes : ie,

LSQL = "select date from Test where date= " & Date()

 

[crujazz]

Perhaps you can post a copy of your calling query ? - from what you suggest it appears that your query is calling the function 4 times.

You will need to define the LSQL string BEFORE opening the recordset though!

Also the Date function will need to be defined outside of the quotes : ie,

LSQL = "select date from Test where date= " & Date()

Hearby my calling query: SELECT gdt() AS Expr1 FROM Test;

 

[crujazz]

query is based on simple table: id, name, date
in query3 the following argument: SELECT gdt() AS Expr1 FROM Test; based on

Function GDT() As String
Dim db As Database, Lrs As DAO.Recordset, LSQL As String, LT As String
Set db = CurrentDb()
LSQL = "select date from Test where date =" & Date
Set Lrs = db.OpenRecordset(LSQL, dbOpenDynaset)
If Lrs.EOF = False Then
LT = Lrs("date")
Else
LT = "Not Found"
End If

Lrs.Close
Set Lrs = Nothing
Set db = Nothing
GDT = LT
End Function

 

[GUIDO22]

Just curious - have you got 'x' number of records with only 4 rows with the date equal to todays date?

My guess is that your SELECT statement is calling GDT() for each and every record in the source table! Hence, you are getting an output of todays Date repeated four times..... where the Dates match


Perhaps you should call this only once but from within VB code alone - do not reference from a query which is where the problem lies.

What do you wish to do with this data once retrieved ?

 

[crujazz]

thnx, i'll give it a try....

The purpose of this all is that i have an excel sheet with tons of data based on date. What i want is to import that excelsheet and let access calculate, but it ain't that easy cause each date has a value what decrease. For example:
date L R
07-07-09 198 36
08-07-09 120 98
09-07-09 119 20

I want to calculate the difference between 07-07-09 and 08-07-09 for L
when calculate R i have to make ghost field with the value 210 so that R=(210-36)/12

and then nextrecord.......