Using Date as Criteria in Recordset

[grgimpy]

I've created a combo box which displays all the possible Dates to choose from. I'd like to make a recordset based on the Date chosen in the combo box. I've pasted the code below, which formats the combo box date and sets the expression I built in the recordset equal to the specific date as criteria. I want to build a recordset that only contains data from the Date selected in the combo box.

When I run the code I get run-time error 3061, too few arguments, expected 1.

Any suggestions would be greatly appreciated.

Dim SimpleDate
SimpleDate = Format(Me.Date, "mmm dd yyyy")

Dim rs As Recordset, db As Database
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("select [Ni-Au-Date/Time],[Ni-Au-Shift],[Ni-Au-RunHoursExpected]," _
        & "Format([Ni-Au-Date/Time],'mmm dd yyyy') as FormatDate" _
        & " from [Ni-Au-Production Log]" _
        & " where [Ni-Au-Shift]=1 and FormatDate='" & SimpleDate & "'" _
        & " order by [Ni-Au-Date/Time]")

 

[Remou]

Even though you have an alias FormatDate, I do not think Access will allow you to use it in the where statement. Also, it is safer to use YYYY/MM/DD format. The delimiter for dates is a hash mark (#). How about:

[tt]"SELECT [Ni-Au-Production Log].[Ni-Au-date/time], " _
& "[Ni-Au-Production Log].[Ni-Au-Shift], " _
& "[Ni-Au-Production Log].[Ni-Au-RunHoursExpected], " _
& "Format([Ni-Au-Date/Time],'mmm dd yyyy') AS FormatDate " _
& "FROM [Ni-Au-Production Log] " _
& "WHERE [Ni-Au-Production Log].[Ni-Au-date/time]=#" _
& Format(Me.Date, "yyyy/mm/dd") _
& "#) AND [Ni-Au-Production Log].[Ni-Au-Shift])=1) " _
& "ORDER BY [Ni-Au-Production Log].[Ni-Au-date/time]"[/tt]

Date is not a good name for a field or control as it is a reserved word.

 

[PWise]

in Jet Sql date Qualifiers are #

Dim SimpleDate
SimpleDate = Format(Me.Date, "mmm dd yyyy")

Dim rs As Recordset, db As Database
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("select [Ni-Au-Date/Time],[Ni-Au-Shift],[Ni-Au-RunHoursExpected]," _
        & "Format([Ni-Au-Date/Time],'mmm dd yyyy') as FormatDate" _
        & " from [Ni-Au-Production Log]" _
        & " where [Ni-Au-Shift]=1 and FormatDate=[COLOR=red]#[/color]" & SimpleDate & "[COLOR=red]#[/color]" _
        & " order by [Ni-Au-Date/Time]")

 

[grgimpy]

I tried inserting the pound signs before and that does not work either. I also tried using Remou's string and the problem with that is the original [Ni-Au-Date/Time] is not in the same format as the combobox. This is why I re-formatted [Ni-Au-Date/Time] into FormatDate. If I can't include FormatDate in the where statement, then I guess I'll just have to create a query that puts [Ni-Au-Date/Time] in the same format. Then, create the recordset based on that query.

Any other suggestions would be appreciated though.

 

[Remou]

Is Ni-Au-Date/Time in the table a date? Is the combobox a date? If so, the format will not matter. Queries are safest with ANSI dates and may not work at all if the date is neither American nor ANSI.

 

[oharab]

in Jet SQL, dates MUST be formatted #mm/dd/yyyy# to be recognised as a date.

Dim SimpleDate as string
SimpleDate = Format(Me.Date, "mm/dd/yyyy")

Dim rs As Recordset, db As Database
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("select [Ni-Au-Date/Time],[Ni-Au-Shift],[Ni-Au-RunHoursExpected]," _
        & "Format([Ni-Au-Date/Time],'mmm dd yyyy') as FormatDate" _
        & " from [Ni-Au-Production Log]" _
        & " where [Ni-Au-Shift]=1 and [Ni-Au-Date/Time]=#" & SimpleDate & "#" _
        & " order by [Ni-Au-Date/Time]")

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[Remou]

Ben O'Hara
A few quotes:

[tt]PHV (MIS) 12 Dec 06 13:07
Furthermore, to avoid ambiguity on date:
If Not IsNull(DLookUp("UNITID","tbl_AnnualServInsp","UNITID = '" & Forms!frm_AVServInspect!UNITID & "' And [date] = #" & Format(Forms!frm_AVServInspect!date,[red]"yyyy-mm-dd"[/red]) & "#"))[/tt]
Date format: thread701-1265176 (my red)

[tt]The ISO date format is not ambiguous even in different countries. Its format is fixed to [red]yyyy-mm-dd[/red] and is understood by all the databases normally used by ASP. You must notice that months and days under 10 must be preceded by a leading zero, so, 02 instead of 2. Finally, the numbers are separated by dashes (-) instead of slashes (/). [/tt]

http://www.4guysfromrolla.com/webtech/tips/t022202-1.shtml

(my red)


[tt]SkipVought (Programmer) 26 Oct 06 20:17



Date is a reserve word. I would not name a field Date

Are both txtDate1 & txtDate2 strings in mm/dd/yyyy format?

A REAL DATE is a NUMBER not a STRING.

Today's DATE is 39016. That DATE VALUE can be FORMATTED for display in a number of different ways that does not alter the VALUE. Using the FORMAT function returns a STRING that lokks like a date but is NOT A REAL DATE.

Bottom line: Either work with ...

ALL DATES or

ALL STRINGS that are FORMATTED to collate in YYYY MM DD sequence.[/tt]
strange date problem: thread701-1294807

Quite apart from my own experience that a format yyyy/mm/dd works very well, so I think my statement "Queries are safest with ANSI dates and may not work at all if the date is neither American nor ANSI." (22 Jan 07 16:07 ) is correct.

 

[oharab]

Remou,
You are quite right, Date is a bad field name, I didn't pick that up as it was being referenced absolutely and wasn't causing the issue.
Iit didn't occur to me Jet SQL could use ANSI dates, especially as ,in my head, ANSI compatible is yyyy-mm-dd, it's something I shall use in the future.
Coming from the UK, switching between UK & US date formats is a royal PITA! At least using ANSI makes it clear.

Sorry for cunfusing things.


Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[RoyVidar]

I drop in a little comment too, "yyyy/mm/dd" fails where date separators are different from "/", for instance where I live.

[tt]Format(Date, "yyyy/mm/dd") -> 2007.01.23[/tt]

which leads to syntax errors in queries. I prefer ISO 8601 (

http://en.wikipedia.org/wiki/ISO_8601)

"yyyy-mm-dd".

Allen Browne has a good explanation on that

http://allenbrowne.com/ser-36.html.

See also why the "mm/dd/yyyy" format is dangerous in international apps, and "mm\/dd\/yyyy" is encouraged in stead.

Whats not always easy to spot, is issues of implicit coercion of dates. In the original post, the content of the date control (here disregarding that we don't know whether is a string or a date) is formatted to a string, and placed in a variant, making it a string subtype.

[tt]Dim mydate ' is a variant
Dim dt as Date

dt = DateSerial(2007, 5, 1) ' May 1. 2007, right?

mydate = Format(dt, "mmm dd yyyy")

Debug.Print mydate, TypeName(mydate)[/tt]

Now, on my setup, this is displayed in the immediate pane (ctrl+g) as

[tt]05.01.2007 String[/tt]

This is bad for two reasons
1 - that date means 5. January 2007 where I live
2 - this gives 3075 Syntax error in date ... in queries.

I guess people in UK would get a workable date from this, but slightly off ;-)

So my recommendation is to only format a date when concatenating it into an SQL string, then use unambiguous format, or format when you are sure it is the final operation on the date/string - you're not intending to coerce it back to date again

Roy-Vidar

 

[Remou]

You are right Ben O'Hara, I should have used dashes, the slash is a habit. I first saw the yyyy/mm/dd format on Tek-Tips and, like you, found that it would suit me very well. The rule is that dates must be unambiguous (I'll skip the quotes , so I suppose that is why the slah passes. It evens works on my tiny website.