Selasa, 09 Februari 2016

RE: [MS_AccessPros] InputBox in Expression Builder in a query


I think a more complete picture of your data structure would get you better answers from the list. You need at least 2 tables.




lngProductID …..Other descriptive fields

datShelfLife = integer denoting number of days after production






Other fields specific to the Production Run




SELECT tblProducts.*, tblProductionRuns.lngProductionRunID, tblProductionRuns.datDateOfProduction, DateAdd("d",[tblProducts].[intShelfLifeDays],[tblProductionRuns].[datDateOfProduction]) AS UseBy

FROM tblProductionRuns INNER JOIN tblProducts ON tblProductionRuns.lngProductID = tblProducts.lngProductID;





Bob Peterson


From: []
Sent: Monday, February 08, 2016 3:09 PM
Subject: Re: [MS_AccessPros] InputBox in Expression Builder in a query


Thanks for the direction, but that won't work for me, only because adding 365 is just an example. I need to be able to add days(i.e. 186, 248, 365, 455) to whatever date they enter, 


What it is, is I have a all our products stored in the database, and I have a form that contains all the build info, like how many cases go on a pallet, how it has to be stacked, type of materials to use, and the code date that is printed on the box. This code date is based on the date of production, plus # of days after to equal the Best If Used By date.


If I can not configure that in an expression in the query, would it be possible to do it in the form that displays the query info. For example have a text box that they enter the production date, and have the value of another textbox display the production date plus the extra days, through VBA code, expression, or macro?


I attempted to add the below to no avail...


Private Sub BIUB_AfterUpdate()
BIUB.Value = ProductionDate.Value + 365
End Sub


Where ProductionDate is the text box that they enter a date, and BIUB as the textbox that adds days.  I have the forms separated for aother reason but the added days would not change in each form.


Thanks so much,





On Sunday, February 7, 2016 4:35 PM, "Duane Hookom [MS_Access_Professionals]" <> wrote:




IMO, there is no place in Access user interface for parameter prompts. I would always use a control on a form for grabbing user input.


However, if you want to do this, you can use something like this assuming you want to add a year to the entered date:


DateAdd("yyyy",1,[Enter Date of Production])


The user will only be prompted once since the same parameter is used twice.


Duane Hookom, MVP
MS Access


Date: Sun, 7 Feb 2016 12:16:12 -0800
Subject: [MS_AccessPros] InputBox in Expression Builder in a query

I am looking for a way have a query to ask a user enter a date, use the date that they enter and output the date they enter plus a fixed amount. i.e. Expression field ProductionDate       [Enter Date of Production], they enter 2/7/16, and have the expression add 365 days to output 2/7/17.
I came across the function for InputBox, and have successfully had it output   [Enter Date of Production], but have been unable to have accept the input, and output the expression ProductionDate + 365.

Any Help would be greatly appreciated.
Thanks so much,



Posted by: Robert Peterson <>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)



Tidak ada komentar:

Posting Komentar