Access Date Range Query

Is MDY stored as a Date or a Text field in your Access database?

The behavior you are describing makes it sound like Text.  It seems to me like your SQL statement should work if MDY is stored as a Date field.

It is stored as text in the database...do I need to modify my query?

Thanks

could possibly be date format. what format is the date held in the database


Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE MDY >= #" & format(txtStartdate.Text,"Short Date") & "# AND MDY <= #" & format(txtEnddate.Text,"Short Date") & "#"

Short Date takes what is in your control panel settings
or you could use the mask

MM/DD/YYYY instead of Short Date

failing that, it could be MDY  that is not in that format, so maybe wrap that

Any chance of actually modifying the underlying field type?  That would be the easiest solution :)

if its text and you aint got no nulls then you could wrap MDY with cdate

I'm not sure if this would work from ASP.net (that's what you're using, right?), but this would work from the Access environment:

Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE CDate(MDY) >= #" & txtStartdate.Text & "# AND MDY <= #" & txtEnddate.Text & "#"
 

CDate converts MDY to Date.

Make sure the format of the two textboxes is set to Short Date. Then run this statement:

Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE MDY >= Me!txtStartdate AND MDY <= Me!txtEnddate

Make sure the cursor is not in either box when the code executes.  Let me know what happened please.

Unless you have a specific reason for storing this date in a text field, in addition to being the easiest solution, the BEST solution is to store your dates as dates.

Thanks for all the suggestions.

Rockiroads, your code didnt produce any results, same as mine (Using 4/1/2008 and 5/1/2008)

mbizup, your code did produce results however, it had two issues.
1. It didnt grab the very first date of the table. ( I used dates 4/1/2008 thru 5/1/2008) It didnt grad the first 4/1/2008 record, it started with the 2nd one.
2. Along with the records in the date range it grabed many others, for ex the last one was 10/27/2008

GrayL
Your code also didnt produce any results (Using 4/1/2008 and 5/1/2008)

Well my concern with that  is I am writing to the database from a text field that has "mm/dd/yyyy" in it...will that cause an issue?

Try this...

Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE CDate(MDY) BETWEEN #" & txtStartdate.Text & "# AND  #" & txtEnddate.Text & "#"


<mm/dd/yyyy>
If this actually gets stored, then yes, it would be an issue.

However, with additional code (which could be a headache, depending on your app), you could store Nulls or some real date instead.

Are there any text values currently in the MDY field that are not actually Dates?

mbizup

Thanks for the code...it is returning the correct date range but its still skipping the first record. have you ever seen this before?

No everything in the MDY is mm/dd/yyyy

Did that get rid of the extraneous records?

<No everything in the MDY is mm/dd/yyyy>

If this were my project, I would change the field type to Date    **** Making a backup first and scrutinizing it in a test environment ****

That however would not resolve the missing record issue - which I'll hazard a guess has something to do with time stamps.

I've got a meeting this afternoon, but will check on this later if no one chimes in.

Can you post a text-art table of sample data?

Retracting previous comment.

I'm forgetting that were dealing with a text field, and you've already told me that the format is "mm/dd/yyyy".  I'm assuming that is consistent - with no deviations and no time stamps involved.

Let me know if I'm wrong about that.  Otherwise I'll go with that assumption, give it some more thought and check back in later.

Your assumption is correct. All of the MDY fields are mm/dd/yyyy no deviation...

OK, in an effor to fix this issue I have converted the MDY fiel to data/time in access and have the format set as "Short Date"

I still have the problem were the first record is not being returned.

I dont know if this matters but in the MDY field is the day or month is a single digit it is showing 4/1/2009 not 04/01/2009

that doesnt matter. changing it to a date field is probably for the best.
remember what I said earlier, changing it to short date uss th settings of the control panel. this is only an issue if your app is used internationally.

regarding missing record, default time would be 00:00 so not sure if thats its but you could try putting a format around MDY as well

format(MDY,'MM/DD/YYYY')

or change to the format to be mm/dd/yyyy instead of leaving as short date

Thanks rockiroads, actually I already put the date format in as the date was being returned with the time too.

Now it appears the query is missing records again.

One step forward another back =(

or better still, to leave it without fixed formatting, if you want to see your leading zeros, you can change the format of the date str in control panel. this will then be reflected by the "Short Date" use. Better to use those predefined strings than fixed formatting in the table definition

ok, lets get this right

date format of MDY is MM/DD/YYYY (or Short Date)

date entry by users is in MM/DD/YYYY format

Im wondering whether you should use parameter queries if its asp.net

This is converting string input to date
Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE MDY >= #" & Convert.TodateTime(txtStartdate.Text) & "# AND MDY <= #" & Convert.todatetime(txtEnddate.Text) & "#"

Are you using OleDbCommand to run this?

Actually I got the query to return all results within the date range ,except for first record, with my original query.

Yes the database is in mm/dd/yyyy format...I have a calendar control on my form that automatically formats the date to mm/dd/yyyy

I cant figure out why the query is skipping the first records for the particular begin date.

Below is my code behind....maybe I am doing something else wrong.

        '## Sets query string ##
        'Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE CDate(MDY) BETWEEN #" & txtStartdate.Text & "# AND  #" & txtEnddate.Text & "# ORDER BY Vis_Num ASC"
        'Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE CDate(MDY) >= #" & txtStartdate.Text & "# AND MDY <= #" & txtEnddate.Text & "#"
        'Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE MDY >= Me!txtStartdate AND MDY <= Me!txtEnddate"
        'Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE MDY >= #" & Format(txtStartdate.Text, "Short Date") & "# AND MDY <= #" & Format(txtEnddate.Text, "Short Date") & "#"
        Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE MDY >= #" & txtStartdate.Text & "# AND MDY <= #" & txtEnddate.Text & "# ORDER BY Vis_Num ASC"
        'Dim strQuery As String = "SELECT Vis_Num,MDY,Status,Employee,Vision_Type,Department,Present_Method,Proposed_Method,Ats_Number,Drawing_Number,MO_Number,Critical_Success,Add_Emp1,Add_Emp2,Add_Emp3,Add_Emp4,Add_Emp5,Add_Emp8, FROM Data WHERE Vis_Num = ?"
        Dim cmdSearch As New OleDbDataAdapter(strQuery, dbConn)
 
        'Takes value from search box and uses it to qry against Vis_Num in database
        cmdSearch.SelectCommand.Parameters.AddWithValue("MDY", txtStartdate.Text)
        cmdSearch.SelectCommand.Parameters.AddWithValue("MDY", txtEnddate.Text)
 
        Dim reader As OleDbDataReader
 
        Try
            dbConn.Open()
            reader = cmdSearch.SelectCommand.ExecuteReader()
 
            '## execute reader ##
            reader.Read()
 
            If reader.HasRows Then
 
                '## Binds dropdown box to database fields ##
                ddVisnum.DataSource = reader
                ddVisnum.DataTextField = "Vis_Num"
                ddVisnum.DataValueField = "Vis_Num"
 
                '## Inserts value into the dropdown box ##
                ddVisnum.Items.Insert(0, New ListItem("Open"))
                ddVisnum.DataBind()
 
            Else
 
                '## Exits sub, clears values and shows error ##
                txtError.Visible = True
                txtStartdate.Text = ""
                txtEnddate.Text = ""
                ddVisnum.Items.Clear()
                Exit Sub
 
            End If
 
        Catch exdd As Exception
            Response.Write(exdd)
 
        End Try

Select allOpen in new window

You are using parameter queries so surely you just need to be "?" in the query?


Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE MDY >= ? AND MDY <= ? ORDER BY Vis_Num ASC"

You got the same name so unsure, I assume it will still work with the two parameters.

Hmmm, not sure if the ? would need to be wrapped with #

Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE MDY >= #?# AND MDY <= #?# ORDER BY Vis_Num ASC"


Something I used before in the past was to format in YYYYMMDD then checking it

Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE Format(MDY,'yyyyMMdd') >= " & format(txtStartdate.Text,"yyyyMMdd") & " AND Format(MDY,'yyyyMMdd') <= " & format(txtEnddate.Text,"yyyyMMdd")

note format in asp.net its gotta be yyyyMMdd  (capital MM, rest lowercase)

Thanks for the suggestion...however I am still not getting the first record even after using ?

rocki,

When I try your last code I do not get any results.

I tried changing the yyyyMMdd to ddMMyyyy and still didnt get anything

Does this query inadvertently add the EndDate + 1 day?

ie: are we off on both ends of the range?

If so, try this:

Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE MDY + 1  BETWEEN #" & txtStartdate.Text & "# AND  #" & txtEnddate.Text & "#"

Nope it only skipping the first record.  Good thought though.

did u run this query in access to see what you get?

At the risk of stating the obvious, that seems really strange.

Can you try the following and let me know what happens?

Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE MDY + 1  BETWEEN #" & cdate(txtStartdate.Text) & "# AND  #" & cdate(txtEnddate.Text) & "#"

Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE MDY + 1  BETWEEN #" & Dateadd("d",-1, cdate(txtStartdate.Text)) & "# AND  #" & cdate(txtEnddate.Text) & "#"

Not sure if these functions are available to you in ASP.net...

Miriam, vb.net is being used so cdate is available.

Thanks to everyone but with the help of EE I was able to get this resolved.

The problem was with my Datareader....I was inadverntly activating it twice.. with the reader.Read() command and ddVisnum.Databind()

Ill have to look through all the posts again to decide who all gets points. Thanks again for all your effort.