473,326 Members | 2,438 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Query Select Error; Datetime in vb6 from db Ms.Access

How do I interpret the declaration time 'AM' and 'PM' in Ms.access Query

I have Query Select in vb6.0
to get data from Ms.access database

Expand|Select|Wrap|Line Numbers
  1.     SQLSelectdbRAS = "SELECT a.DN, b.DIN, c.PIN, c.UserName, b.Clock, d.ItemName " & _
  2.                      "FROM ((ras_Device a INNER JOIN ras_AttRecord b ON a.DN=b.DN) " & _
  3.                      "LEFT JOIN ras_Users c ON b.DIN=c.DIN) " & _
  4.                      "LEFT JOIN ras_AttTypeItem d ON b.AttTypeId=d.ItemId " & _
  5.                      "WHERE b.DIN=c.DIN AND Format(b.Clock, 'mm/dd/yyyy')=#" & _
  6.                      Format(dateNow_, "mm/dd/yyyy") & "# AND " & _
  7.                      "Format(b.Clock, 'mm/dd/yyyy hh:nn:ss')>=#" & _
  8.                      Format(dateNow_, "mm/dd/yyyy 4:00:00PM") & "# AND " & _
  9.                      "Format(b.Clock, 'mm/dd/yyyy hh:nn:ss')<=#" & _
  10.                      Format(dateNow_, "mm/dd/yyyy 11:00:00PM") & "#"
  11.     Debug.Print SQLSelectdbRAS
  12.     Set rsFinger = da_GetRs(SQLSelectdbRAS)
But when i run that query, i get this message error
'Syntax error in date in query expression 'b.DIN=c.DIN AND Format(b.Clock, 'mm/dd/yyyy')=#10/19/2010# AND Format(b.Clock, 'mm/dd/yyyy hh:nn:ss')>=#10/19/2010 4:00:00P10# AND Format(b.Clock, 'mm/dd/yyyy hh:nn:ss')<=#10/19/2010 11:00:00P10#'.

I want to interpret 4:00:00P10 in 4:00:00 PM and 11:00:00P10 in 11:00:00 PM

Can anyone help me to fix that query?

Regards,
Octo
Oct 19 '10 #1
2 3111
colintis
255 100+
for this formatting, it would be much simplier using this
Expand|Select|Wrap|Line Numbers
  1. "b.Clock BETWEEN #" & _
  2. Format(dateNow__, "mm/dd/yyyy 16:00:00") & _
  3. "# AND #" & _
  4. Format(dateNow__, "mm/dd/yyyy 23:00:00")
By default, MS Access have full date/time as mm/dd/yyyy hh:mm:ss (in 24hr style) in SQL statements, so no necessary formatting needed to the table fields.
Oct 19 '10 #2
Dear Colintis,

Thank you so much for your help
it's work..

GBU always..

Regards,
Octo
Oct 20 '10 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Jonas Gorauskas | last post by:
I am having this one problem on an app I am writing where it shows the following error on some error trapping that I did: Query failed: Error Number: 1064 Description: You have an error in...
0
by: Morten Gulbrandsen | last post by:
Hi programmers, Good morning ;-) I think something is wrong, Running windows I believe first I have to set some password, Which creates some user, Then I can grant the required privileges, for...
3
by: dstewart | last post by:
Situation: One common MySQL database server on SuSE 9.1 with all updates. Uses 'rinetd'. Has entries for the appropriate IP addresses of all servers. NOTE: If the appropirate entries are NOT in...
0
by: Mike Thomas | last post by:
This one must be very simple. I am trying to run an SQL Serv 7.0 stored proc from a VBA module in an Access 2000 app. This stored proc runs fine when I remove the parameters from the stored proc...
1
by: Dalan | last post by:
I'm experiencing a Query Syntax Error with an Access 97 Db. Actually, the query performs as expected when adding any new records or editing existing ones and even deleting records, EXCEPT when the...
1
by: maneeshkhare | last post by:
I am trying to update a field in Access db (.mdb) via a DataSet using the following code. I am continously getting this error: Has anybody seen this before? 1. If I try using the same code for...
9
by: dennist685 | last post by:
I created a web project named 'Access' in C:\Inetpub\wwwroot\Access and dropped an .mdb there. I dragged a DetailView to the form, configured it to show two fields of a table, and got...
0
by: db2query | last post by:
Hello Guys, Am posting two queries here which are exactly same except for an always false condition( 0 = 1) to influence the access path selection, to my surprise i am getting 183 records if the...
0
by: rickmedlin | last post by:
I know this has been posted on elsewhere but I'm stuck. I'm using the following append query to copy an Access query to Excel: INSERT INTO . SELECT * FROM Test; This isn't the real table...
2
by: rickmedlin | last post by:
I know this has been posted on elsewhere but I'm stuck. I'm using the following append query to copy an Access query to Excel: INSERT INTO . SELECT * FROM Test; This isn't the real table...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.