Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Access 2007 Date Filter


(!)

ibanezsicko's Avatar
ibanezsicko ibanezsicko is offline
Member with 87 posts.
THREAD STARTER
 
Join Date: Mar 2010
Experience: Intermediate
28-Oct-2010, 07:02 AM #1
Access 2007 Date Filter
Hi,

I have a form with a listbox on it which displays all the data in one of my tables. I also have a combo box which displays a list of dates (short date) from the table.

I'm trying to query the table based the combobox to get all the records that came after that date.

However, at the moment, when i enter a date, and the list is requeried, the data is not correct.

For example, there is data for everyday (from 12/10/2010 to 26/10/2010) so if i select 12/10/2010 from the combo box, id expect to see records from 13/10 to 26/10.

what i actually get is some records starting at 24/10.

If i change the query so that it looks for records before the selected date, nothing is returned.


Any suggestions as to whats going on?


Cheers
Lewis
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
28-Oct-2010, 07:20 AM #2
Can you sjow a Screenshot of your Query with the Criteria that you are using?
ibanezsicko's Avatar
ibanezsicko ibanezsicko is offline
Member with 87 posts.
THREAD STARTER
 
Join Date: Mar 2010
Experience: Intermediate
28-Oct-2010, 07:52 AM #3
Hi,

there is also other combo boxes which filter other data in the table, these all work fine when the 'date1' filter is taken out.

This code is taken from the Change event of the first combobox(filters by selected user).
This is a brand new form and so is not perfect. as it is now, i select the filter criteria from the combo boxes and then select a user last (as the requery is done here at the moment).

eventually, i will have if statements which use alternative queries to filter the results, but for now im just getting everything working using the 'filter user' combobox. unfortunatley, when i have added in the filter 'date1' to the query, i have hit problems.

(the following query has been split onto seperate lines just now for easier reading. not like this in the code)
----------------------
Private Sub filterUser_Change()

Dim qry As String

qry = "SELECT HISTORY.ID, HISTORY.User, HISTORY.Description, HISTORY.Object, HISTORY.[Object ID], HISTORY.[Object Description], HISTORY.[Object Quantity], HISTORY.Subject, HISTORY.[Record Date], HISTORY.[Record Time]
FROM HISTORY
WHERE (((HISTORY.User) Like '" & Me.filterUser & "*')
AND ((HISTORY.Description) Like '" & Me.Description & "*')
AND ((HISTORY.Object) Like '" & Me.Object & "*')
AND ((HISTORY.Subject) Like '" & Me.Subject & "*')
AND ((HISTORY.[Record Date]) > " & Me.date1 & "));"

Me.List.RowSource = qry
Me.List.Requery
Me.List2 = ""

End Sub
----------------------

See anything wrong?

Lewis
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
28-Oct-2010, 09:03 AM #4
When using a Date Variable in VBA SQL you need to add # symbols either side
AND ((HISTORY.[Record Date]) > #" & Me.date1 & "#));"
ibanezsicko's Avatar
ibanezsicko ibanezsicko is offline
Member with 87 posts.
THREAD STARTER
 
Join Date: Mar 2010
Experience: Intermediate
28-Oct-2010, 10:39 AM #5
hi, just tried that.

unfortunately, no luck. result is the same, if i select the earliest date and requery, i get no results. if i select the next earliest, it results in dates from 24/10 and over (when there are dates from 12/10 to 26/10)


Lewis
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
28-Oct-2010, 10:45 AM #6
Have you tried doing this in a Query, i.e. not using VBA SQL?
If you can get it to work in a query you can convert it to VBA SQL code.
If you can post oe email me a database with the table in it I can probably do it for you.
__________________
OBP
I do not give up easily
ibanezsicko's Avatar
ibanezsicko ibanezsicko is offline
Member with 87 posts.
THREAD STARTER
 
Join Date: Mar 2010
Experience: Intermediate
28-Oct-2010, 10:52 AM #7
ok, ill email you the database.

the form is called frmHistory

When you open the database, just rightclick and close the login form that opens.
when you open the frmHistory form you will get an error, its because there will not be anyone logged in, so click End and it will behave as normal.


Do you need the db in a particular format?

What is your email address?


Lewis
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
28-Oct-2010, 11:24 AM #8
I have Private mailed you my email address.
ibanezsicko's Avatar
ibanezsicko ibanezsicko is offline
Member with 87 posts.
THREAD STARTER
 
Join Date: Mar 2010
Experience: Intermediate
28-Oct-2010, 11:31 AM #9
thank you,

I've just sent it to you now.






Lewis
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
28-Oct-2010, 02:19 PM #10
Lewis the problem is the Null search Fields on the form, especially the Subject one, the Subject entries only start 24/10/2010, so even using the Like operator the records start at the 24th.
So I would suggest that you use VBA code that only builds on the SQL string if the filter field is not null.
ibanezsicko's Avatar
ibanezsicko ibanezsicko is offline
Member with 87 posts.
THREAD STARTER
 
Join Date: Mar 2010
Experience: Intermediate
28-Oct-2010, 07:57 PM #11
Hi,

would you mind sending me your version of the database please?


Thanks
Lewis
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
29-Oct-2010, 05:06 AM #12
Do you want to create selective SQL VBA to avoid using the Null fields?
ibanezsicko's Avatar
ibanezsicko ibanezsicko is offline
Member with 87 posts.
THREAD STARTER
 
Join Date: Mar 2010
Experience: Intermediate
29-Oct-2010, 05:19 AM #13
yep, that was my intention.

But anything that has the date working is something to build on.



Lewis
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
29-Oct-2010, 05:32 AM #14
OK, I have to go for a while, so I will send you something later.
ibanezsicko's Avatar
ibanezsicko ibanezsicko is offline
Member with 87 posts.
THREAD STARTER
 
Join Date: Mar 2010
Experience: Intermediate
29-Oct-2010, 05:34 AM #15
ok, no problem.

Thank you very much.


Lewis
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


Similar Threads
Title Thread Starter Forum Replies Last Post
Solved: Access 2007 Sync'd Combo Box Value Not Displaying johnthegeek Business Applications 6 26-May-2010 11:54 PM
Microsoft Access 2007 Jenawest Business Applications 3 20-Jan-2010 05:44 PM
Solved: Labels in Access 2007 Renamed&Closed Business Applications 2 20-Jan-2009 02:56 PM
Access 2000 App displays #Name? in Access 2007 pdesjardins Business Applications 1 31-Jul-2008 05:00 AM
Access 2007 Reports skins2848 Business Applications 4 10-Jul-2008 12:52 PM

WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑