Calling select query to move through date/time fields!

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!!!!
DavidMcGarryAsked:
Who is Participating?
 
Snarf0001Connect With a Mentor Commented:
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
 
Snarf0001Commented:
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
 
DavidMcGarryAuthor Commented:
Ok ,

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

How do i do this?

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
pambooCommented:
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
 
pambooCommented:
Format(strDate, "dd/mm/yyyy")
0
 
Snarf0001Commented:
Format(strDate, "dd/MM/yyyy")

Lowercase m's are regarded as minutes, not months.
0
 
DavidMcGarryAuthor Commented:
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
 
DavidMcGarryAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.