Field value from multiple records VBA

[fat controller]

It is huge, and loaded with stuff that I cannot release into the public domain I'm afraid.

I have just stepped through the code, and it works through the function until it gets to this line:

Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)

Then it jumps to the Error Handler

 

[pbaldy]

Understandable. You can use this to check the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

There's something misspelled or a reference that Access can't resolve.

 

[fat controller]

Handy, thank you

OK, I have stepped through again, with that in place and it returns:

SELECT VehicleTypes FROM TypesKnown WHERE EmployeeNumber='0770979'

I have attached a screenshot of the table structure, just in case you can see something I am not.

The employee number is stored as text, not numerically as the leading zero is required elsewhere.

 

[fat controller]

It decompiles fine, so I am going to do a quick backup, compact and then reboot the PC just to put my mind at rest that I am not missing something by not doing the immediately obvious - I will be back shortly.

 

[pbaldy]

There's no "s" in VehicleType.

 

[fat controller]

There's no "s" in VehicleType.

Did I tell you I was blind as well as stupid?

Well, it now works perfectly; once again, you have got me sorted. I am raising a glass of Scotch in your honour, and if you were here there would be a bottle with your name on it.

 

[pbaldy]

We've all done it. Now, tell me where "here" is so I can come claim my bottle.

 

[fat controller]

We've all done it. Now, tell me where "here" is so I can come claim my bottle.

On the very fringes of South West London, about halfway between the M3 motorway and Heathrow airport. You would be most welcome (but I hope you can hold your drink, as I am a Scotsman by birth and we have a reputation to uphold!

If I wanted to get really fancy and put the result in order, am I right in thinking that it would go after the WHERE clause, and would be "VehicleType, ASC"?

 

[pbaldy]

No comma, but yes. Something like "VehicleType ASC" in Allen's sort argument should work.

I've actually been in that area before. My older daughter lived in England (Bury St Edmunds) for 3 years and I visited a few times. I've been on the tube between Heathrow and London and been driven through the area after being picked up by her.

She and her husband traveled to Scotland many times, and acquired a taste for good Scotch. I regretfully never made it up there. Sadly, I would disappoint you. I'm more of a wine drinker (Italian heritage), and I'm sure your reputation would remain unscathed (you would drink me under the proverbial table).

 

[fat controller]

If you were picked up at Heathrow, and (I presume) went round the M25 to head for Bury you would have been fairly close to me - - I am quite close to Staines (or Staines Upon Thames as it is now called, because the council wants to make it sound posh!)

If you get a chance, for a reasonably priced blended whisky to use with a mixer, try Stewart's Cream of the Barley; comes in a rectangular bottle, is really inexpensive, and goes really well with some Canada Dry (American Ginger Ale I think is the closest). Failing that, go for a single malt and only add more single malt.....

My other half likes wine more than I do, but to be fair I will drink almost anything (as long as its legal, and not Gin)

I can't say I have any knowledge of your part of the world, as I am yet to leave this green isle - maybe one day.....

 

[Cronk]

The criteria date has to be in the format mm/dd/yyyy

Change
ServiceDate = Format(Me.ServiceDate, "dd\/mm\/yyyy")
to ServiceDate = Format(Me.ServiceDate, "mm\/dd\/yyyy")