Recordset problem

[jmc014]

Hello there,

I've been trying to get this function to return data from my Data Table via the Recordset function:

Dim Re As DAO.Recordset

Set Re = CurrentDb.OpenRecordset("SELECT * FROM Data WHERE [Date]=#" & Forms!My_Form!Date & "#")

Can anyone push me in the right direction since the above code does nothing.

Thanks for any help provided.

 

[PHV]

Use the Re.Fields collection to get the data you want.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

Actually your code does do something ... specifically it retrieves all rows satisfying your WHERE clause into a recordset. In order to do something with those rows you need something like

Dim Re As DAO.Recordset

Set Re = CurrentDb.OpenRecordset("SELECT * FROM Data WHERE [Date]=#" & Forms!My_Form!Date & "#")

Do Until Re.EOF
    [blue]... Process Data in the recordset here ...[/blue]
    Re.MoveNext
Loop

 

[jmc014]

Hi,

I have did have the rest of the code, I only showed the first part because I'm sure the problem lies there:

To keep it simple, this is what I'm trying:

Dim Re As DAO.Recordset
Set Re = CurrentDb.OpenRecordset("SELECT * FROM Data WHERE [Date]=#" & Forms!My_Form!Date & "#")

Do Until Re.EOF

Forms!My_Form!Id = Re!Id

Re.MoveNext
Loop

And... nothing.

I'm sure that the problem is with the syntax of the Set Re line.

 

[MajP]

Need to read up on how to debug your code. A couple to simple things to answer this question.

Dim Re As DAO.Recordset
dim strSql as string
strSql = "SELECT * FROM Data WHERE [Date]=#" & Forms!My_Form!Date & "#"
'Is the sql correct?
debug.print strSql
Set Re = CurrentDb.OpenRecordset(strSql)
'Did you return any records
debug.print re.recordcount

Do Until Re.EOF
 'is the value correct
 debug.print re!Id
 Forms!My_Form!Id = Re!Id
 Re.MoveNext
Loop

 

[jmc014]

That was a quick reply...

No, nothing.

I'm sure that the problem lies with the positioning of the # marks.

 

[jmc014]

Ok...

I get a value using:

Set Re = CurrentDb.OpenRecordset("SELECT * FROM Datos")

This means that there is a syntax error with the rest of the condition.

Let's play a little more.

 

[Golom]

This means that there is a syntax error with the rest of the condition.

Not necessarily. If there was a syntax error SQL would be complaining about it.

How is the [Date] field being populated?

If it is being set with Now() (i.e. date and time) then just testing it against a date (i.e. a date with zero time) will probably return no records. Try something like

Set Re = CurrentDb.OpenRecordset( _
"SELECT * FROM Data WHERE CDate(Int([Date]))=#" & Forms!My_Form!Date & "#")

 

[jmc014]

The date input is manual. The Forms!My_Form!Date format shows 05/04/2010.

I've tried your code and still, nothing. This is strange, If I change the code for any other value other than the date) I get a value.

It seems that the code is correct but the returned value is always null or no match found (probably because of the date format)

 

[Golom]

I surmise that you are using a dd/mm/yyyy format to input the [Date] field. Note however that Access assumes the mm/dd/yyyy format when an input date is ambiguous (i.e. day <= 12).

Because of that 05/04/2010 would be interpreted as May 4th and not as April 5th. You need to use an unambiguous format like yyyy-mm-dd to avoid this problem.

 

[MazeWorX]

strSQL = "SELECT Data.*, Data.Date " _
       & "FROM Data " _
       & "WHERE (((Data.Date)=" & "#" & [YourDateControlNameHere] & "#" & "));"

BTW naming a date field in your table as 'Date' is a bad idea. Within access there is the Date function .... think about naming your fields, controls etc with unique names eg
Date you could name as mDate or Dateworked ... you get the idea. The same applies to tables, forms, controls and queries .. tblMyTable, frmMyForm, ,txtMyTextControl qryMyQuery

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[dhookom]

Change the date format using code like:

Dim Re As DAO.Recordset
dim strSql as string
strSql = "SELECT * FROM Data " & _
  "WHERE [Date]=#" & _
  Format(Forms!My_Form!Date, "mm\/dd\/yyyy") & "#"
'Is the sql correct?
debug.print strSql
Set Re = CurrentDb.OpenRecordset(strSql)
'Did you return any records
debug.print re.recordcount

Do Until Re.EOF
 'is the value correct
 debug.print re!Id
 Forms!My_Form!Id = Re!Id
 Re.MoveNext
Loop

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Duane, Golom already gave an unambiguous date format solution at 5 Apr 10 15:27

 

[dhookom]

PH,
I'm not sure jmc014 would understand how to implement

You need to use an unambiguous format like yyyy-mm-dd to avoid this problem.

Duane
Hook'D on Access
MS Access MVP

 

[jmc014]

Thank's for everyone's input.

With regards to MazeWorX comment about naming tables. fields and forms. In order to make things clear I normally change the names of my fields and commands when writing my problems, most of my objects have spanish names. The actual naming does not match any Access functions, hence, no problems there.

I finally got it to work, the problem seemed to be the unambiguous date format as mentioned above.

Thanks for all your assistance.