6 Replies - 1042 Views - Last Post: 14 January 2015 - 08:54 AM Rate Topic: -----

#1 Nova6112   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 90
  • Joined: 28-November 14

how can i use select query to select both Date And Time in DTPicker

Posted 14 January 2015 - 06:43 AM

Hi ,

I'm using 'Now' to specify the current Date and Time to insert values. when i try to fetch values from DB tables i'm searching using DTPicker. but the values in DB table like this dd-MMM-yyyy hh:mm:ss tt, i can't fetch this values using DTPicker I'm using this Query
dim dat1 as date
dat1 = Format$(DTPicker1.Value, "dd-MMM-yyyy")
sql = "select * from onhand where dat=#" & dat1 & "#"


Please help me how can i search, Thanking you in advance

Is This A Good Question/Topic? 0
  • +

Replies To: how can i use select query to select both Date And Time in DTPicker

#2 djjeavons   User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: how can i use select query to select both Date And Time in DTPicker

Posted 14 January 2015 - 07:45 AM

Hi

Is this an Access database (guessing as you are using # to enclose your date)?

If so, you can use the Format function within your SQL statement to format the dat column:

sql = "SELECT * FROM onhand WHERE Format(dat, ""yyyy-MMM-dd"")='" & dat1 & "'"



HTH
Was This Post Helpful? 0
  • +
  • -

#3 Nova6112   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 90
  • Joined: 28-November 14

Re: how can i use select query to select both Date And Time in DTPicker

Posted 14 January 2015 - 08:11 AM

ya, but its not my problem, I've inserted a date value with time like this (14-Jan-15 08:40:00 PM).
and i have to select all the records in that paricular date using DTPicker. For that only i can't fetch the records. How can i design query for that
Was This Post Helpful? 0
  • +
  • -

#4 djjeavons   User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: how can i use select query to select both Date And Time in DTPicker

Posted 14 January 2015 - 08:23 AM

So do you only want to get a record that matches 14-Jan-15 08:40:00 PM or do you want to match all records that have 14-Jan-15 regardless of time part?

If it is the latter (all that match 14-Jan-2015) which is what I assume then the code I posted should work. Although I see that I didn't match your initial format. so to correct:
sql = "SELECT * FROM onhand WHERE Format(dat, ""dd-MMM-yyyy"")='" & dat1 & "'"


If dat1 is equal to 14-Jan-2015 then the above query will match all records that have a dat value of 14-Jan-2015 regardless of what time.
Was This Post Helpful? 0
  • +
  • -

#5 Nova6112   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 90
  • Joined: 28-November 14

Re: how can i use select query to select both Date And Time in DTPicker

Posted 14 January 2015 - 08:37 AM

sorry djjeavons i tried but its not working it shows there is no record.
Was This Post Helpful? 0
  • +
  • -

#6 djjeavons   User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Re: how can i use select query to select both Date And Time in DTPicker

Posted 14 January 2015 - 08:45 AM

Sorry, my fault again. I have used a format that is for the full year (century and decade) where as you only have decade. Just change the format in the SQL string to suit your needs. At the end of the day you are looking to generate an SQL statement that looks like:

SELECT *
FROM onhand
WHERE Format(dat,"dd-MMM-yy")='14-Jan-15'


Was This Post Helpful? 1
  • +
  • -

#7 Nova6112   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 90
  • Joined: 28-November 14

Re: how can i use select query to select both Date And Time in DTPicker

Posted 14 January 2015 - 08:54 AM

Really thank you so much dear djjeavons, now its working thank you....
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1