Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1077
  • Last Modified:

Access Date Range Query

Hello,

The query below is partially working for me and by that I mean  if I input dates of 4/1/2008 and 5/1/2008 results are returned but if I use 4/1/2008 and 12/31/2008 no results are returned.

The database has records from 2007 through 2009 so I know records exists. There are gaps in the database data range, ie there isnt a records for each day.

Any thoughts on why this is happening?

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

Open in new window

0
ITHelper80
Asked:
ITHelper80
  • 12
  • 12
  • 11
  • +1
2 Solutions
 
mbizupCommented:
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.
0
 
ITHelper80Author Commented:
It is stored as text in the database...do I need to modify my query?

Thanks
0
 
rockiroadsCommented:
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

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
mbizupCommented:
Any chance of actually modifying the underlying field type?  That would be the easiest solution :)
0
 
rockiroadsCommented:
if its text and you aint got no nulls then you could wrap MDY with cdate
0
 
mbizupCommented:
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.

0
 
GRayLCommented:
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.





0
 
mbizupCommented:
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.
0
 
ITHelper80Author Commented:
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)
0
 
ITHelper80Author Commented:
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?
0
 
mbizupCommented:
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.
0
 
mbizupCommented:
Are there any text values currently in the MDY field that are not actually Dates?
0
 
ITHelper80Author Commented:
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
0
 
mbizupCommented:
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.
0
 
mbizupCommented:
Can you post a text-art table of sample data?
0
 
mbizupCommented:
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.
0
 
ITHelper80Author Commented:
Your assumption is correct. All of the MDY fields are mm/dd/yyyy no deviation...
0
 
ITHelper80Author Commented:
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
0
 
rockiroadsCommented:
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')
0
 
rockiroadsCommented:
or change to the format to be mm/dd/yyyy instead of leaving as short date
0
 
ITHelper80Author Commented:
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 =(
0
 
rockiroadsCommented:
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
0
 
rockiroadsCommented:
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?

0
 
ITHelper80Author Commented:
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

Open in new window

0
 
rockiroadsCommented:
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.


0
 
rockiroadsCommented:
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)
0
 
ITHelper80Author Commented:
Thanks for the suggestion...however I am still not getting the first record even after using ?
0
 
ITHelper80Author Commented:
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
0
 
mbizupCommented:
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 & "#"

0
 
ITHelper80Author Commented:
Nope it only skipping the first record.  Good thought though.
0
 
rockiroadsCommented:
did u run this query in access to see what you get?
0
 
mbizupCommented:
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...
0
 
mbizupCommented:
Sorry... Drop the +1 from both of my copy/paste jobs

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

Dim strQuery As String = "SELECT Vis_Num FROM Data WHERE MDY  BETWEEN #" & Dateadd("d",-1, cdate(txtStartdate.Text)) & "# AND  #" & cdate(txtEnddate.Text) & "#"
0
 
rockiroadsCommented:
Miriam, vb.net is being used so cdate is available.
0
 
ITHelper80Author Commented:
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.
0
 
rockiroadsCommented:
Another thing, in your code, you should check for HasRows before you attempt the read

            reader = cmdSearch.SelectCommand.ExecuteReader()

            If reader.HasRows Then

                '## execute reader ##
                reader.Read()



and if you are only interested in the first record being returned, you could add in select top 1
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.

Join & Write a Comment

Featured Post

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.

  • 12
  • 12
  • 11
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now