Solved

Calling select query to move through date/time fields!

Posted on 2004-04-06
8
365 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
 
LVL 3

Expert Comment

by:pamboo
ID: 10765378
Format(strDate, "dd/mm/yyyy")
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A short article about a problem I had getting the GPS LocationListener working.
This is an explanation of a simple data model to help parse a JSON feed
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

867 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now