Solved

Access Date Range Query

Posted on 2009-04-03
36
1,065 Views
Last Modified: 2012-05-06
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
Comment
Question by:ITHelper80
  • 12
  • 12
  • 11
  • +1
36 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 24060734
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 24060749
It is stored as text in the database...do I need to modify my query?

Thanks
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24060769
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24060773
Any chance of actually modifying the underlying field type?  That would be the easiest solution :)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24060796
if its text and you aint got no nulls then you could wrap MDY with cdate
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24060799
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24060832
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24060950
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 24060958
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 24060976
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24061020
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24061147
Are there any text values currently in the MDY field that are not actually Dates?
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24061291
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24061926
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24061936
Can you post a text-art table of sample data?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24062008
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 24062247
Your assumption is correct. All of the MDY fields are mm/dd/yyyy no deviation...
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24062868
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 65

Expert Comment

by:rockiroads
ID: 24062951
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24062963
or change to the format to be mm/dd/yyyy instead of leaving as short date
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24062978
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24062980
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24063115
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 24063151
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24063210
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24063260
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 24063265
Thanks for the suggestion...however I am still not getting the first record even after using ?
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24063306
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24063435
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 24063474
Nope it only skipping the first record.  Good thought though.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24063613
did u run this query in access to see what you get?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24063627
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
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 24063648
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24063706
Miriam, vb.net is being used so cdate is available.
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24063738
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
ID: 24063741
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

708 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

20 Experts available now in Live!

Get 1:1 Help Now