Passing specific ComboBox info to a Query

Hi - hope someone can help.

What I'm trying to do is to select service dates within a 53 week year - and
the year doesn't always start on the 1st Jan. When I use specific criteria in
the ServiceDate field in the query (dd/mm/yyyy): 

"Between #28/12/2009# And (#28/12/2009#+371)" 

it works fine. 

When I use a text box (set to dd/mm/yyyy) and set the criteria to 

"Between Forms!FRM_FormName!TextBox and (Forms!FRM_FormName!TextBox +371)"

it also works fine

I don't want the user to have to remember the exact start date of each year,
so I created a tbltablestartdate table; with 3 fields - ID, year and
yearstartdate. 

On the form I created & bound a combobox to that table, but only display the
year. I want to let the user select the year (yyyy) but use the yearstartdate
(dd/mm/yyyy) as the criteria in the query. I tried substituting 'Combobox'
for 'TextBox' but that doesnt work, as (presumably) the combobox is passing
the data for the year, not the yearsstartdate.

I'm sure that it must be possible, but not sure how - any help would be much
appreciated.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

0
pwdpwd
2/8/2010 9:54:16 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
642 Views

Similar Articles

[PageSpeed] 20

You should set the combobox's bound column to 3 (or whatever the column number 
is that contains YearStartDate.  I assume that YearStartDate is a date field.

Also, I would make sure that the query understood the data type.

Between CDate(Forms![FRM_FormName]![ComboBox]) and 
DateAdd("d",371,Forms![FRM_FormName]![ComboBox])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

pwdpwd via AccessMonster.com wrote:
> Hi - hope someone can help.
> 
> What I'm trying to do is to select service dates within a 53 week year - and
> the year doesn't always start on the 1st Jan. When I use specific criteria in
> the ServiceDate field in the query (dd/mm/yyyy): 
> 
> "Between #28/12/2009# And (#28/12/2009#+371)" 
> 
> it works fine. 
> 
> When I use a text box (set to dd/mm/yyyy) and set the criteria to 
> 
> "Between Forms!FRM_FormName!TextBox and (Forms!FRM_FormName!TextBox +371)"
> 
> it also works fine
> 
> I don't want the user to have to remember the exact start date of each year,
> so I created a tbltablestartdate table; with 3 fields - ID, year and
> yearstartdate. 
> 
> On the form I created & bound a combobox to that table, but only display the
> year. I want to let the user select the year (yyyy) but use the yearstartdate
> (dd/mm/yyyy) as the criteria in the query. I tried substituting 'Combobox'
> for 'TextBox' but that doesnt work, as (presumably) the combobox is passing
> the data for the year, not the yearsstartdate.
> 
> I'm sure that it must be possible, but not sure how - any help would be much
> appreciated.
> 
0
John
2/8/2010 12:47:49 PM
Hi John

Many thanks - that works fine

John Spencer wrote:
>You should set the combobox's bound column to 3 (or whatever the column number 
>is that contains YearStartDate.  I assume that YearStartDate is a date field.
>
>Also, I would make sure that the query understood the data type.
>
>Between CDate(Forms![FRM_FormName]![ComboBox]) and 
>DateAdd("d",371,Forms![FRM_FormName]![ComboBox])
>
>John Spencer
>Access MVP 2002-2005, 2007-2010
>The Hilltop Institute
>University of Maryland Baltimore County
>
>> Hi - hope someone can help.
>> 
>[quoted text clipped - 24 lines]
>> I'm sure that it must be possible, but not sure how - any help would be much
>> appreciated.

-- 
Bob Mozley

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

0
pwdpwd
2/8/2010 3:06:01 PM
Reply:

Similar Artilces:

Get Emails and info
1. I need to know how to copy emails from one mailbox to another. 2. I need to have all of the emails from one account forwarded to another account. This needs to be done without the mailbox owners knowing it is happening. My company president has asked me to do this for an ongoing investigation. - you can use Exmerge to extract an existing mailbox - can setup a mailbox to forward all inbound messages to another mailbox (user properties | Exchange General | Delivery Options), however, if a user sends a message to himself with a delivery receipt, he/she will also receive a delive...

Is It Possible To Pass Parameters to A Pass Through Query
My assumption is that's it's not. At work I use ODBC to connect to our oracle database with Access 2003. There are cases where using a pass through query runs much much faster and I then use it in a make table query to make a local table. In access you can use brackets [] to have it ask for input. May I assume there is no way to do anything like that in a pass through query? Create a PassThrough query and use code to assign the SQL to this query filtering it with a parameter, and then run it e.g Dim MyVariable As Integer MyVariable = InputBox("Please select a Number&quo...

Sql Query for Single & parent item Quantity
Hi All I would like to know if there Sql Query to convert the quantities of Parent item to be Single item , i would explain it with an example single item Itemlookupcode is 123456 and Qty 1 Parent item (outer pack of single item) like 6 cans of cola drink forms a pack which can be sold as single as well as pack item Itemlookupcode 123456-06 Qty 2 I need an sql query to produce result like 123456 QTY = 13 and 123456-06 Qty =0. Formula = Qty of Single item + Number of pieces in a single pack * Qty of parent item it would be great help that if it would be possible in sql Thanks ...

Combobox | Activex Controls | Tab
Hi, I have created several combo boxes on the same spreadsheet but I am unable to tab between them. I think the reason I can't tab between them is because they are not all part of one larger form. Is there some way that I can tab between multiple combo boxes on the same worksheet? -- gduron ------------------------------------------------------------------------ gduron's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33239 View this thread: http://www.excelforum.com/showthread.php?threadid=538282 ...

Date query "Excel VBA"
Is there anyway i can use vba code to create a user input box on a sale worksheet that will filter the sales orders and only display the order that equal the date entered into the input box by the end user. I am already using the autofilter feature but would like to take it on step further and make it easier for the admin clerk. Thank-Yo -- Message posted from http://www.ExcelForum.com You didn't like yesterday's suggestion???? http://groups.google.com/groups?threadm=4110273A.B3C20CDD%40msn.com "avilla <" wrote: > > Is there anyway i can use vba code to cr...

Pass multiple selected values from list box to table
How do I pass multiple selected values from a list box to a table? I've found code to use the values as criteria in a query but would now like to store the values in a table (at least temporarily) thanks in advance. jack Pass selected values from a list box to a table? I don't really think you want to do this. Can you describe more about what you are trying to accomplish? You just store raw data in a table. You don't store manipulated data in a table. Maybe you need a query to do what you want to do... -- Ryan--- If this information was helpful, please indi...

How to pass an object from C++
Hello! We have a C# asp.net web application that is using a COM dll We use the tlbimp to be able to use the COM dll from C# asp.net web application The method InitRules shown below is located in the COM dll. It can be seen below the text Original. Method InitRules below is called from C# but we want to pass an object of type Handle_DS in addition to all the other parameters. You can see the modified InitRules below marked Modified Note the object of type Handle_DS is a C++ object that is created from C# asp.net application and pass into the InitRules I have also copied the whole idl fil...

100% Passing Guaranteed in All I.T. Exams/Certifications at 1st Attempt Hi Friends, To pass all types of I.T Exams/Certifications with mind blowing results in 1st attempt just visit http://www.itreal
100% Passing Guaranteed in All I.T. Exams/Certifications at 1st Attempt Hi Friends, To pass all types of I.T Exams/Certifications with mind blowing results in 1st attempt just visit http://www.itrealexams.com/ and get real time Q&As, Brain Dumps, Real Exams, Study material, E-Books, Video Exams and Labs for your all type of I.T Certification Exams. ...

date diff expression in query
I am trying to calculate the difference between two dates 'End Date' and 'Date of Visit' using a query. This is the expression I used: TimeFrame:[Date of Visit] - [End Date]. However, 'End Date' is present in both tables I have used to create this query 'tbl_NewInfo' and 'tbl_Foloowup'. I want the 'End Date' from 'tbl_NewInfo'. How do i specify this in the expression and thereby prevent the error msg I am getting? Thankyou You must fully qualify the field name like: [Table Name].[Field Name] I use a naming convention that resu...

Passing arguments to procedures
There is something fundamental that I have failed to grasp about passing arguments to procedures. I have a form featuring a set of unbound controls displaying dates and the corresponding days of the week. (For some reason, the long-date format, on my computer, does not include the latter, as the documentation suggests it might, so it has to be separately calculated and displayed.) A default date is displayed in each control, on Form_Open. This can then be manually adjusted by a pair of ‘up’ and ‘down’ buttons associated with each day/date control pair. In the case of the control ‘St...

sql room booking query
hai, I am Doing room booking .for eg if a user enter the Need from date as 2/10/2010 and Need to 7/10/2010.booked if another user go for booking so i have to check three conditions.for availability. 1)if another user enter 23/09/2010 to 2/10/2010 is nt possible 2)Also datetime shouldnt be between 2/10/2010 and 7/10/2010 3)also from 7/10/2010 to any other date greater than 7/20/2010 nt possible. pls give hw can i write the querry???? vidya (u64122@uwe) writes: > I am Doing room booking .for eg if a user enter the Need from date as > 2/10/2010 > and Need to 7...

Out of office to specific domains
I understand that it's possible to set the Out of Office message to go only to certain external domains, but I can't find any details on how to do it. Thanks in advance for any help. what version Exchange? I don't know of a way to do that, natively with Exchange... <randykessler@gmail.com> wrote in message news:1127253921.825020.74520@g44g2000cwa.googlegroups.com... > I understand that it's possible to set the Out of Office message to go > only to certain external domains, but I can't find any details on how > to do it. Thanks in advance for any help. &...

crosstab query values
I have a crosstab query that I've created for a Rolling 12 month report. It works great but now I need to add another value to the query. I know that you can only have 1 value field, 1 column field and multiple row fields. My current value field is ShipQty. I need another value field of Price. Is there a way around to get this output? Thanks in Advance Kim P -- Message posted via http://www.accessmonster.com That works, but now my months are not in order. How can I get them to be in chronological order so that they show up correctly on my report. Duane Hookom wrote: >You can poss...

Locating Info In New Excel Spreadsheet When Compared To Old One
Hello, I'm a neophyte regarding Excel spreadsheets, so the answer to my question may be obvious to those experienced in it. On a semi-weekly basis, I receive an Excel spreadsheet that is identical in every way to the previous one except that new cells have been added. Neither I nor the sender of the spreadsheet has any control over where the new cells will be added within the file. With some files having over 22,000 cells, it's extremely difficult and time-consuming to visually search for those cells which are new. Each cell, whether new or old, has info, including d...

query problem 05-09-07
Hi, I have two tables. One for employees Info, the other one for employees completed tasks. In employee’s info table, employee ID is primary key while in employee completed tasks table the employee ID is foreign key. Moreover, in the employee completed tasks table there are more than one tasks for one employee. I made a query for showing all employee‘s info and task. The problem is when I run the query, only those who have a record in the employee tasks table just show, while who do not have record do not appear. I need to sum the completed tasks even if there is no data. Any help pleas...

IT CertificationzzzZ.....100% GUARANTEED PASS.......!!!@!!!
IT CertificationzzzZ.....100% GUARANTEED PASS.......!!!@!!! Hi guys, If you are serious about your professional career and pass IT Certification exam in first attempt then Visit http://www.FreeExamKing.com and get latest Questions And Answers Dumps for Microsoft, Oracle, Cisco, CompTIA and many more, i have used and found it very helpful. ...

Passing data between forms
Hi I am working on a project someone else started, and he has this code: If STR_FormName = "FMS_ToDo" Then Page47.SetFocus He obviously expects the name of the last form to be here, but it is not. In fact, when I put Option Explicit at the top - this field was undefined. How is this supposed to work? Stapes ...

Pass Parameter to stored procedure in pass thru query
How do I pass a parameter to a stored procedure in a pass thru query? I'd like to enter the parameter in a control on a form and then run the pass thru query and pass the parameter to it. I've tried this, but haven't been successful. Any ideas? Thanks. On Sun, 8 Jul 2007 18:04:26 -0500, "SAC" <sac@somewhere.com> wrote: >How do I pass a parameter to a stored procedure in a pass thru query? > >I'd like to enter the parameter in a control on a form and then run the pass >thru query and pass the parameter to it. > >I've tried this, b...

You will pass all the exams at 1st attempt No more headaches and worry about spending the time and money to attempt another exam again. You will pass all of your exams, every time www.FreeExamKing.com
You will pass all the exams at 1st attempt No more headaches and worry about spending the time and money to attempt another exam again. You will pass all of your exams, every time www.FreeExamKing.com ...

Where used query
Is there a utility that I can use to see if a table or query is used in an Access database? Is there a way to open a module as a searchable string? Thanks. Todd - You can open any existing module, or clik on the New tool in the Modules Window to open the VBA editor. From there, Ctrl F or the binocular tool to bring up the Find dialog box. In here, select 'Current Project' to search all code in the database for the table or query name you enterin the 'Find What:' box. As for looking for a table or query in queries, I wrote this query a while bac...

#Error in Query
Hello, I have a query with calculates production for a machine in terms of tonnes/hr. A use will input the starting engine hours, starting tonnes, ending engine hours, and ending tonnes for each day. I have an expression in a query which will subtract the starting and ending hours/tonnes and then divide by one another. This works great unless maintenance was being done on the machine and no production was acheived. You then get 0/0 which produces the #Error message. I posted this question once and someone told be to write this for my expression: Expr1: IIf(IsError(([TonneEnd]-[TonneStar...

Customer not appearing in Collection Query
Hi, I am on GP9 SP3 and have created a collection query, however some customers do not appear in that query even though they should. I have done/checked the following 1. Statement to field is populated with a valid email address 2. Customer is not on hold 3. The customer does not have do not send letter box marked 4. Run checklinks on collections 5. Reconciled the debtor 6. Run checklinks on debtor master and Transaction Open tables 7.Created a new query for that one debtor and it says 0 customers found Can you please assist and let me know where I am going wrong. Thank V can you se...

Like clause used in query builder
Hi, a combo box value is used to a pop-up new open Form. Where in that form a list box row source used that combo box value in query builder as a creteria using LIKE Clause and is not working, anyone can point out what should be a correct way to do it? LIKE "*###*" -->where ### is the combo box value of an existing open Form SELECT dshet_apr.Number, dshet_apr.Title_Description FROM dshet_apr INNER JOIN datasheet_d ON dshet_apr.Number = datasheet_d.Number WHERE (((dshet_apr.Title_Description) Like "* [Forms]![frmm]![combo1] *") AND ((datasheet_d.Dis)=[Forms]![frmm]![co...

Export Customer Info
I am going to be integrating my RMS software with Quickbooks 2005 very soon. For a couple of years now, we have opperated via manual entry. Therefore, I will need to do some modification to both databases to ensure that they mesh up properly for the integration. One major problem that I forsee is the fact that we have hundreds of customers for whom we track sales in RMS. However, in Quickbooks we have only tracked information for customers with in-store accounts or store credits. I assume that I will need to add these customers that are in RMS, but not Quickbooks to the Quickbook...

OnInitDialog and passing argument
Hello, I want to use a dialog for three different purpose so every time dialog should come up with a different situation. How can I pass argunment(s) to the OnInitDialog window message of the dialog ? Is it possible? Thanks Smith "JSmith" <jsmithmitra@yahoo.com> wrote in message news:u4ajmu$nEHA.3900@TK2MSFTNGP10.phx.gbl... > Hello, > > I want to use a dialog for three different purpose so every time dialog > should come up with a different situation. How can I pass argunment(s) to > the OnInitDialog window message of the dialog ? > Is it possible? The...