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
Solved

Calling select query to move through date/time fields!

Posted on 2004-04-06
8
367 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 22

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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 3

Expert Comment

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

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 22

Accepted Solution

by:
Snarf0001 earned 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.

839 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