Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calling select query to move through date/time fields!

Posted on 2004-04-06
8
Medium Priority
?
370 Views
Last Modified: 2010-04-17
I have this major problem, dont know what to do!!!!!!

Im using an Access Database
Im using VB.NET


this is my code to move through a table with a 'Date/Time' format. The field is called register_date
        -------------------------------------------------------------
        DimrsVal As ADODB.Recordset
        Dim sSQL As String
        Dim strDate as Date
       
        strDate = txtDate.Text
         
sSQL = "SELECT tblTimes.Register_Date FROM tblTimes " & _
            "WHERE tblTimes.Register_Date=#" & Format(strDate) & "#"
       ---------------------------------------------------------------
It doesnt seem to see the date, strDate

I know this is a formatting problem but i dont know how to do it?PLEASE HELP.URGENT!!!!
0
Comment
Question by:DavidMcGarry
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 23

Expert Comment

by:Snarf0001
ID: 10765161
Try specifying the format type:

  Format(strDate, "Short Date")

The function might not be correctly converting, for example it might be formatting as a datetime (#01/01/2003 12:00:00 am#) when you're only looking for the date portion.
If that doesn't work, it would be helpful if you could step through the code and post what value is actually being returned by Format(strDate).
0
 

Author Comment

by:DavidMcGarry
ID: 10765344
Ok ,

Its being formatted in MM/DD/YYYY. I want to format the query in DD/MM/YYYY

How do i do this?

0
 
LVL 3

Expert Comment

by:pamboo
ID: 10765352
In access DB ,  the Date field format depends on ur machine Regional Date settings value.

For eg: it may be mm/dd/yyyy  or dd/mmm/yyyy etc., formats.

Since u r not using the DatePicker control in the front end to get the Date value . U have to format the textbox value to the regional setting type.

For eg. If it is in dd/mm/yyyy format . Format it to like this

Format(tstDate, "dd/mm/yyyy")

If both ur application and mdb file going to be in same machine , I wish u could use DatePicker control of VB .NET.
It will also get the same default settings what mdb is going to use . No conflict will occur . Try using it or using the Format functions as above.

Hope this helps.

- Rajan
0
Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

 
LVL 3

Expert Comment

by:pamboo
ID: 10765378
Format(strDate, "dd/mm/yyyy")
0
 
LVL 23

Expert Comment

by:Snarf0001
ID: 10765392
Format(strDate, "dd/MM/yyyy")

Lowercase m's are regarded as minutes, not months.
0
 

Author Comment

by:DavidMcGarry
ID: 10765557
ok the formatting is ok now with this:


sSQL2 = "SELECT tblTimes.Register_Date " & _
"FROM tblTimes " & _
"WHERE format(tblTimes.Register_Date,'DDMMYYYY')='" & Format(strDate, "DDMMYYYY") & "'"

 rsVal2 = m_Conn.Execute(sSQL2) 'Execute query.

Ok, but when i have a check if its the end of file, it says there are no records, when i know for a fact in the Register_Date field there is one value: 03/02/2004

If Not rsVal2.EOF Then
MsgBox("Student previously registered for this class")
            Exit Function
End If


Why wont it see it,

0
 

Author Comment

by:DavidMcGarry
ID: 10765568
Is there a format problem with the actual field in the db?

I just saw something called input mask in t he design view of the table, will this do anything?
0
 
LVL 23

Accepted Solution

by:
Snarf0001 earned 2000 total points
ID: 10765648
I can't speak for the Access format function, but .NET format IS case sensitive.  Format(strDate, "DDMMYYYY") is going to return "DD04YYYY".  You have to use it as ddMMyyyy.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is about my first experience with programming Arduino.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question