• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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!!!!
0
DavidMcGarry
Asked:
DavidMcGarry
  • 3
  • 3
  • 2
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
Snarf0001Commented:
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now