SQL Query in MS Access - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query in MS Access

Author

Topic

wussupbuddy
Starting Member

16 Posts

Posted - 2008-12-04 : 03:01:45

I have the following query in VB

rs1.Open "select * from accesscontrol where empcode=" & rs!empid & " and s_datetime=#" & startDate & "#"

Now the problem is that s_datetime is in datetime format (has the time as well) while startDate is only date, so the two never matches up. Is it possible to retrieve just the date part in mm/dd/yyy format. I tried convert but it doesn't seem to work in access.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 03:12:34

is startDate datetime?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-04 : 04:56:31

rs1.Open "select * from accesscontrol where empcode=" & rs!empid & " and s_datetime >='" & startDate & "' and s_datetime < DATEADD(d,1,'" & startDate & "')"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-04 : 04:58:33

Should have been:

rs1.Open "select * from accesscontrol where empcode=" & rs!empid & " and s_datetime >=#" & startDate & "# and s_datetime < DATEADD(d,1,#" & startDate & "#)"

wussupbuddy
Starting Member

16 Posts

Posted - 2008-12-04 : 09:23:09

So I added dateadd function and I seem to be getting the correct records. I'm entering these records into a new table but this has the time along with it. Is it possible to get rid of the time part when I'm inserting it. i can't change the column properties.

strSql = "Select empcode,s_datetime into accesscontrol from [;Database=\\server\universal.mdb;PWD=blah].transactions As T where T.s_datetime>=#" & startDate & "# and T.s_datetime<=#" & DateAdd("d", 1, endDate) & "#"
db.execute strsql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 09:25:59

[code]strSql = "Select empcode,dateadd(dd,datediff(dd,0,s_datetime),0) into accesscontrol from [;Database=\\server\universal.mdb;PWD=blah].transactions As T where T.s_datetime>=#" & startDate & "# and T.s_datetime<=#" & DateAdd("d", 1, endDate) & "#"
db.execute strsql[/code]

wussupbuddy
Starting Member

16 Posts

Posted - 2008-12-04 : 10:08:48

I'm getting the error too few parameters. expected 1. I guess dateadd doesn't work inside the query for access. I'm using adodb.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 10:11:14

try with an alias


strSql = "Select empcode,dateadd(dd,datediff(dd,0,s_datetime),0) as s_datetime into accesscontrol from [;Database=\\server\universal.mdb;PWD=blah].transactions As T where T.s_datetime>=#" & startDate & "# and T.s_datetime<=#" & DateAdd("d", 1, endDate) & "#"
db.execute strsql

wussupbuddy
Starting Member

16 Posts

Posted - 2008-12-04 : 10:50:51

Getting circular reference error for the alias name. if I change the alias name then I get too few parameters error :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 11:03:42

quote:
Originally posted by wussupbuddy

Getting circular reference error for the alias name. if I change the alias name then I get too few parameters error :(

try giving it some other name as alias

wussupbuddy
Starting Member

16 Posts

Posted - 2008-12-04 : 11:06:03

I tried that ..getting Too Few Parameters error

wussupbuddy
Starting Member

16 Posts

Posted - 2008-12-04 : 11:07:30

Can I mail u or something..I have couple of more queries and any help would be very much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 11:09:28

post it here itself. if related to access post in ms access forum

wussupbuddy
Starting Member

16 Posts

Posted - 2008-12-04 : 11:16:08

Cool..so any more ideas for the above query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 12:44:42

one more attempt

strSql = "Select empcode,dateadd(d,datediff(d,0,s_datetime),0) as s_datetime into accesscontrol from [;Database=\\server\universal.mdb;PWD=blah].transactions As T where T.s_datetime>=#" & startDate & "# and T.s_datetime<=#" & DateAdd("d", 1, endDate) & "#"
db.execute strsql

wussupbuddy
Starting Member

16 Posts

Posted - 2008-12-05 : 02:37:52

nope..same issues..anyways I found a workaround to ignore time stamp later in the code..thanks very much for you help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources