SQL Datetime access via date only .... with MS Access database....

Hi jakyll

I'd be inclined to create a parameter query in your access db, which expects one input parameter. You will them be able to execute the query and pass the input parameter directly off your connection object to fill your adaptor. When you choose the stord query as the datasource for the adaptor, you should have an option to bind a control on your form as the expected input parameter.

you may even be able to execute the query by name directly off your connection object.
like:     cn.MyQuery pStartTime


hth

Alan

ps... Kudos to Nico, applying the same format to input param and the field being searched is the equivalent to the SQL server Convert method.

The parameter query
========================
PARAMETERS pStartTime DateTime;
SELECT RunID, Format([StartTime],"dd-mmm-yyyy") ,ProductID
FROM ProductRun
WHERE (((Format([StartTime],"dd-mmm-yyyy"))=Format([pStartTime],"dd-mmm-yyyy")));
========================
 
note: I always use a 3 digit month format to avoid ambiguity when comparing dates.

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

Hi Alan, I see you're reading the Q better as I did as the ? parameter was missed by me.
I'm however afraid that the "varchar(11)" indicates the field being text, thus I also proposed the CDate() function.
As an alternative we might indicate to the questioner that using:
WHERE (((Format([StartTime],"dd-mmm-yyyy"))=Format([Enter StartTime:],"dd-mmm-yyyy")));
is a way in Access to ask for a parameter "on the  run"

Nic;o)

Hi Nico,
the answer to this Q was fullly provided by you; ie... use the Access Format() function in place of the SQL server TSQL Convert() function, that was the answer my friend, perfectly assesed by you, as always.

I was just throwing in my 2c worth, suggesting to use a stored query and pass the imput param. I like that way best because using a stored proc ensures no risk of sql injection. It would be no good to raise a prompt saying "Enter StartTime" in this scenario mate, because Access is not actually running, so the prompt would never be seen; the MDB is merely being used as a 'dumb' data catalog, being manipulated by VB.Net.

Hey, how the hell are ya Nico?  Long time no see.

Alan ";0)

Hi Alan, glad to see you're still active mate. I've posted several times links to your sample .mdb's when people were wondering about images and blob's, thus you're my blob-master :-)

The needs of this questioner aren't quite clear to me, as the title states:
"SQL Datetime access via date only ....  with MS Access database...."
I thought a translation from the MS SQL statement to Access was necessary, thus the Format() and the CDate().

However, when he's using linked tables, then indeed input param passing (or perhaps pass-through queries) would be a better option. Using an Access project would even allow complete access to the MS SQL views and procedures...

Nic;o)