OpenRecordset Problem

[Toga]

I am trying to create a Lot# for a program I am writing and having a problem. The format I want for the Lot # is the numbers in the date (03/04/01 = 030401) followed by the # of lots ran on that day. For example, the Lot# for the 3rd lot run on 03/04/01 would be 030401-3.

Anyway....NP with getting the 030401- part.....now I need the # of lots already ran that day so I can take that number and determine the next Lot#.

If I go into the query portion of access and run the following query.....it works great.

SELECT Count(ToolingProduction.Date) AS CountOfDate
FROM ToolingProduction
WHERE (((Format([Date],"mmddyy&quot)=Format([Forms]![Tooling_Production].[Date],"mmddyy&quot));

I then say strSQL = the above string and use it in dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
and get the following error. "Data Type Mismatch in Criteria Expression" What am I doing wrong? Why does it work fine one way and not the other?

PS....I change "mmddyy" to 'mmddyy' when assigning this string to strSQL. Also, if I remove the WHERE portion of the statement.....I don't get any errors....I just don't get the data I'm looking for.

Thanks for your help!

 

[ghubbell]

Might try:
Dim dtTPDate as Date
dtTPDate = Format([Forms]![Tooling_Production].[Date],"mmddyy&quot

SELECT Count(ToolingProduction.Date) AS CountOfDate
FROM ToolingProduction
WHERE (((Format([Date],"mmddyy&quot)= " & dtTPDate & &quot)"

Or something on these lines to remove the address to the form from the sql statement...

Gord
ghubbell@total.net

 

[Toga]

I think we're close but.....the date field on the form is updated automatically and might read as 3/1/01 8:18:41 AM.

When I do a dtTPDate = Format([Forms]![Tooling_Production].[Date], "mmddyy&quot, it returns 9/7/82. Confused or what here!

Anyway, the query can't find that date in my table so it still doesn't work. Any other ideas????

 

[ghubbell]

Try this one:

Dim strTPDate as String

SELECT Count(ToolingProduction![Date]) AS CountOfDate
FROM ToolingProduction
WHERE (((Format(ToolingProduction![Date],"mmddyy&quot)= " & strTPDate & &quot)"
Gord
ghubbell@total.net

 

[Toga]

Well, that was a good thought but unfortunately it gets me back to the "Data Type Mismatch in Criteria Expression" error.

This is really confusing because like I said before in my initial message.....the query works great in the query design window but I can't just seem to transfer it and use it in the OpenRecordset statement.

Is there another way to skin this cat? I just want to know the number of records for a given date.

 

[ghubbell]

We were better off with the first.. your date has to be a date. Now this has to work!

Dim strSQL As String, Rs As Recordset, db As Database
Set db = CurrentDb
strSQL = "SELECT Count(ToolingProduction.Date) AS CDate FROM ToolingProduction WHERE (((Format([Date],'mmddyy'))= " & Format([Forms]![Tooling_Production].[Date], "mmddyy&quot & &quot)"
Set Rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
MsgBox "Your count is " & Rs!CDate & "!"
Rs.Close

If this fails look carefully at the syntax as I see we've made a couple of boo boos (technical term) above.


Gord
ghubbell@total.net

 

[Toga]

Thanks Gord. I brought your code into the Query designer environment and got it to work fine but I still have the same issue using it with the OpenRecordSet scenario......"Data Type Mismatch in Criteria Expression"

Depending how I modify things I sometimes get the error..."Too Few Parameters, Expected 1"

One work around that will work, which I think can be better described as cobbing vs programing, is to use a list box. If it's a new record, I change it's row property to
Listbox.RowSource = "SELECT Count(ToolingProduction.Date) AS CountOfDate FROM ToolingProduction WHERE (((Format([Date],"Short Date&quot)=Format([Forms]![Tooling_Production].[Date],"Short Date&quot));" If I click on it, I concanate the lot # I've wanted into another Text Box where LotNum is the Control source. I actually changed the listbox around so it looks like a button you would select whereas it's special effects are raised and changes to sunken with a mouse down click. The forecolor is the same as the backcolor so you don't see anything except a button to push. Hey, I told you it was more like a cob! But it does work!

The other thing I was going to try is using an action query via DoCmd.RunSQL "Select INTO". Perhaps when user verify's they want to create a new record, I'll run this query and then take them to the newly created record. If it works, it will at least feel like I didn't create a cobbed up mess!

I do wish I understood why the other will not work though. Why is it that SQL used in the Query designer won't work in the OpenRecordset environment????????

Thanks for your help thus far......Toga

 

[ghubbell]

Toga:
It's all in the syntax. The query in the QBE grid should be:
SELECT Count(ToolingProduction.Date) AS CountOfDate
FROM ToolingProduction
WHERE (((Format([Date],"mmddyy&quot)=Format([Forms]![Tooling_Production].[Date],"mmddyy&quot));

If your getting a parameter error with the one I sent you then it's syntax: it's looking for that one parameter Forms!~~Date. You might try changing to square brackets and bangs "!" instead of any thing near "date". I ran into a similar problem once before as "date" on its own is kind of a reserved word in SQL and VB... If you renamed everything say TPDate, we would probably be ok.

Cobbing the thing is an option...but...tell you what, it's about -20C here today and windy so if you want so send over a snippet I'll be glad to help you out. (Beats shoveling snow...!) Gord
ghubbell@total.net

 

[7ony]

Shamefully, I just glanced over the 'answers' given you. But I think the problem is 2 fold.
1. Dates need to be done like this [SomeDate] > #02/19/2001# with the Date inside #'s.
2. Quotes inside quotes must be "" for a single " or """ for a single " and end or beginning of string. Adssuming MyDate has the date 02/19/2001 in it, to get:
WHERE [SomeDate] = #02/19/2001# AND [Desc]="CAT"
you must type:
"WHERE [SomeDate] = #" & MyDate & "# AND [Desc]=""CAT"""

Hope that helps...
7ony

 

[ghubbell]

7ony:
I hope Toga gets your advice as we poked around with this simple little nightmare for quite some time, did eventually find a solution although using a different approach. Toga? Gord
ghubbell@total.net

 

[Toga]

7ony,

I just picked up your suggestion and have been trying to get it to work but to no avail. Gord & I have tried a bunch of different things and found a couple alternative ways to accomplish it.....but not with the OpenRecordSet method as I had hoped.

I'm at the point of believing that this is just an Access bug wrt to it's ability....or inability to manipulate dates in the OpenRecordSet method. I can get the SQL statement to work great in the query design mode or in the little Listbox that I cobbed up.

If your up for a challenge, I have a little snippet that I'd be glad to email you or anyone for that matter. I really hate giving in but this darn problem is defying logic....at least within the boundaries of my limited understanding thus far.