Question Combobox

MS_Access_Amature

Registered User.
Local time
Today, 09:56
Joined
Nov 10, 2010
Messages
56
I'm trying to build an unbound combo box that allows you to pick 3 options: Current Week, 1 Week Ago, 2 Weeks Ago. How do i calculate this? Say I have 3 months worth of records. When the user chooses 2 weeks ago - I want it to show 2 weeks ago from Now() and only that week.
 
Make sure that you have set these properties of the combo box:
Row Source Type - Value List
Bound Column - 1
Column Count - 2
Column Width - 0cm; Xcm

where X will be the width of the combo box.

In the Load event of the form put this:
Code:
Me.combobox1.RowSource = Date & ";Current Week;" & DateAdd("ww",  -1, Date) & ";1 week ago;" & DateAdd("ww", -2, Date) & ";2  weeks ago"
where combobox1 is the name of the combo box. Obviously you should have a more meaningful name.

In the criteria row of the Date field in your query, you would put something like this:
Code:
BETWEEN [Forms]![[COLOR=Red][B]NameOfForm[/B][/COLOR]]![[COLOR=Red][B]NameOfCombobox[/B][/COLOR]] AND Date()
 
Why when I go to view the form I get a pop up that says enter parameter value.....Form!NameOfForm!NameOfCombobox???
 
Why when I go to view the form I get a pop up that says enter parameter value.....Form!NameOfForm!NameOfCombobox???
headinhands.jpg
 
I forgot to put the the names in the Query...But anyways the combobox doesn't work. It doesn't even show the value list.
 
:) Sorted now?

Not likely because your code:

BETWEEN [Forms]![NameOfForm]![NameOfCombobox] AND Date()

isn't going to do what they requested -
MS_Access_Amature said:
When the user chooses 2 weeks ago - I want it to show 2 weeks ago from Now() and only that week.

So there will need to be some additional calculations to get the start date of the week and the end date of that week.
 
Hmmm... I think the OP isn't clear in his objective. It may well be the case.

So if that's the case, then 2 weeks from TODAY isn't exactly 2 weeks from TODAY. It could also be the end of the week of TODAY and start of the week of 2 weeks from TODAY.

Maybe the OP could clarify the position.
 
I'm filtering a form....

I'm trying to do a TimeClock Database. So the form that I'm working on now is for the administrator so he can look each employee's hours. So the form has the following properties

Record Source: SELECT Punches.DateOfWork AS MyDateOfWork, Punches.TimeIn AS MyTimeIn, Punches.TimeOut AS MyTimeOut, Punches.DayTotal AS MyDayTotal, Punches.* FROM Punches WHERE (((Punches.DateOfWork) Between [Forms]![PunchAdministration]![ComboGoBack] And Date()));

Default View: Continuous Forms

Filter
Code:
 = 'Admin'
 
 
On the header of the form I have a combobox named MyEmployeeFilter with the following porperties
 
Row Source: SELECT mwsecMY.Code, mwsecMY.Name FROM mwsecMY ORDER BY mwsecMY.Name;
 
And the form has 4 textboxes (MyDateOfWork,MyTimeIn,MyTimeOut,MyDayTotal)
 
So What I'm trying to do is have the 3 options so you can view the Current Week, 1 Week Ago, and 2 Weeks Ago....
 
Still not completely clear but this should do it. So going on the premise that if Current week is selected, then you want records from the start to the end of this week. If 2 weeks ago is selected, you want records two weeks from the start of this week to the end of this week.

Load event of form:
Code:
    Dim wkStart As Date
    
    wkStart = Date - (Weekday(Date) - 1)
    
    Me.Combobox1.RowSource = wkStart & ";Current Week;" & DateAdd("ww", -1, wkStart) & ";1 week ago;" & DateAdd("ww", -2, wkStart) & ";2  weeks ago"
Query criteria part:
Code:
BETWEEN [Forms]![[COLOR=Red][B]NameOfForm[/B][/COLOR]]![[COLOR=Red][B]NameOfCombobox[/B][/COLOR]] And DateAdd("d", 7 - Weekday(Date), Date)
 
Is giving me an error message...This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assinging parts of the expression to variables.

I was reading about this and is saying it may be missing # in the expression because is a date. But where?
 
So, where does it throw the error? In the query or in the code?
 
I don't know...as soon as i click Form View the message pops up and it won't let me see Form View it just goes back to design view. I think in the query though because it doen't show me debug.
 
You put it in the Code Builder section?

Let me see the full code and a screenshot of the query in design view.
 
[/code]Query criteria part:
Code:
BETWEEN [Forms]![[COLOR=black]NameOfForm]![NameOfCombobox[/COLOR]] And DateAdd("d", 7 - Weekday(Date), Date)

If in the query you need the parens for the date function (don't need them in VBA but do in the query):
Code:
BETWEEN [Forms]![[COLOR=black]NameOfForm]![NameOfCombobox[/COLOR]] And DateAdd("d", 7 - Weekday(Date[COLOR=blue][B]()[/B][/COLOR]), Date[COLOR=blue][B]()[/B][/COLOR])
 

Users who are viewing this thread

Back
Top Bottom