I have a query that is based on Property & tenants. It runs a report that gives me all the property listed with the tenants & other info for the property's. Sometimes the tenant moves out & the property is Vacant, so i have a Vacant flag in the property table.
But when i run the query it prints out the last tenant. Can i pick up the vacant flag & change the tenant name to Vacant on the report? As i don't want to delete the tenant from the tenant table.
I currently import data into a table and it it has a text field which looks like this: 12,345.67 GBP...I need to use a query to make this a number field so that I can sum it's contents. I've managed to remove the 'GBP' part but can't seem to get rid of the comma?
I am using an append query to move data into another database. One of the fields being imported is a date field in text form (20141201). I need it appear in the final database in text form (01/12/14) I have tried using several date conversions and cant get this work. Ideally i need the final value as a text rather than date.
I need help with an update query that will change records for me based on the date
In the record I have a TEXT record called [GI1] that can have a multitude of data in it. The data can be “EVA”, “CON 10/12/06”, “REQ 10/12/06”, “PAS 10/12/06”, or “B 10/12/06”, and finally “A 10/12/06”
What I need is a query will look at the Data that starts with “B” and change the letter to “A” if the date in the record has passed. {The Date can be any date in any year }
I have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.
The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".
The 3rd LB value chooses a field. The Fieldname Table has field "Field Type"
I want to change the Text box next to the List box to this entry in the Db.
Ie. If I choose a field in the LB, say "Done?", then to update that field it needs to offer me a "yes/no" field type Therefore it changes the TextBox to a Checkbox (or combo with Yes and No as options)
I have a simple add form that will add an entry to one of two tables: Box 1-1, or Box 1-2.
They have the exact same fields. In the add form, I want the user to specify a Rack field and a Box field. Box 1-1 would be Rack 1, Box 1. Box 1-2 would be Rack 1, Box 2 (there will be more tables later, but just using two for now until I get it working).
But right now my form always adds to the table Box 1-1 - I can't get it to switch.
I've added the code I have so far below. The first part is what I'm having trouble with, the second part just uses a Submit button to add a new record - I'm just including it in case it's interfering in some way I'm not seeing. I'm running Access 2007.
Code: Private Sub Switch_BeforeUpdate() If Me.Rack.Value = "1" And Me.Box.Value = "1" Then Form_Add.RecordSource = "Box 1-1" ElseIf Me.Rack.Value = "1" And Me.Box.Value = "2" Then Form_Add.RecordSource = "Box 1-2"
I have a continuous form that displays data from a table. What I am trying to accomplish is to change the format of the field if the string length is equal to 11, to "@@@-@@@-@@@@-@". Otherwise if it is greater or less than 11 then no format.
I have tried it using code:
If Len(Me.FormFieldName)=11 Then Me.FormFieldName.Format = "@@@-@@@-@@@@-@" End If
Is it even possible and if so is my syntax correct or am I way off base?
I need a way for my query to take all rows with the same TellerNum and add Field1, Field2, and Field3 together for those records. So, there may be 20 records with the same TellerNum, and I need Field 1, 2, and 3 to be added together and then totaled for all 20 records to give me 1 grand total. I need this to be done for each TellerNum, so that the results will be a grand total for each TellerNum. So, the results may look something like this:
I need a query's field to change to what is selected in a combo box.
Example,
I select product 1 in the combo box on a continuous form, after the update there is a requery. The form now displays the data from product 1.
I have tried putting [forms]![form1]![combo1] into the field box of the query. But this only makes the form display "product 1" in every row, it does not take any data from the table.
I am looking to get a query to show my list of customers "Grouped By" [CustomerName], that show only the single [CurrentBalance] field for each customer based on the "Last or Highest" [RecordID].
Also, each customer can have up to 4 different [StockType]'s but at least 1 [StockType].So my results would look like this:
I'm having a spot of trouble trying to get conditional formatting to work. I have an overview form which displays current quotes going through the system, the QuoteStartTime field is generated from the Now() command.I Would like if possible to show, 1 hour = Green, 2 hours = Amber and 3+ hours Red.
I've tried variations of hour([QuoteStart])>1 DateAdd("h",1,[QuoteStartTime])
I've done this before with a date only field before, but working with time is taking too much time.
I have a table listing about 20 elements as field names eg FE, CR, NI, TI and so on.
I have built a form which has a combo box listing these elements by selecting "fields" in the property settings of the combo box & next to this combo box i have 2 text box's where the user can input Min & Max values to pass on to the query.
E.g., FE (chosen from the combo box) value between (Text box1) and Text box 2.
I can run the query to give me values between the 2 text box's by using the following formula in the criteria (Between textbox1 and textbox 2).
The issue i have is to be able to select the element from the listbox, input the min & max values identified and be able to pass this to a query so the query can filter based on the field and values passed?
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
So I have created a select query and I am using it as an all purpose search query. I plan on changing the value of SQL in the query based upon what the user selects as the method of searching. So there is a drop down menu, that allows the user to select the method, this will be expanded in the future but for now till I get this working I am not going to bother with that. That being said, I figure once I get a couple of these the work the others should follow suit. Anyway, here is what I have so far.
Code: Dim StrSQL As String Dim qdf As DAO.QueryDef Select Case Me.Combo3
I have a query, and in order for it to run properly, I've set it to look in an unbound combobox on a form to display the necessary data I want, as long as I never close down the form, this is exactly how I want it.However, if I close this form down, and reopen it, I'm asked by messagebox to get the lookup from a different form's combobox, despite saving the correct form's design over and over again, and saving the actual query, more than once, while in design view.when Access changes the criteria, there isn't even a combobox value on the form it changes to.
I have a query that pulls records from a main table based on a check box and I need a macro or code that will set the field value as if it were unchecked.
BTW I am self-taught and new at this so don't make fun of me if the question seems stupid.
I was trying to see if there is a way to convert text to a hyperlink in a query.
Background : I'm pulling information from a lotus notes database using an OBDC connection and storing it into an access database and using that to upload to a sharepoint site. While we are converting over to a sharepoint site, I was trying to make it easy to get back to the original site for each document to check the work.
i have an update query that looks for a product number, updates the cost, and re-calculates the price in two columns based on the salesman's margins. the problem i am having is that it seems to be hit and miss on the first run. if you run it again, it runs the calculations on all the fields. (it has to check / recalculate 16,000 rows.) should this query have vba to make it loop thru the table.
here is the basic layout of the query:
table 1 is newproductq table 2 is Products Field: PriceIn Table: newproductq Update To: [Products].[Cost] UNITS newproductq [PriceIn]/((100-[Margin])/100) PIECES newproductq [PriceIn]/((100-[BrokenMargin]/100)
I have a date field NxtAPayDate that I need to update to the same day in the next calendar quarter. I have an update query set up using DATEADD but it does not change the dates. The table name is AutoPay
I am using the Query Design Grid. This is the SQL code behind the query:
UPDATE AutoPay SET AutoPay.NxtAPayDate = DateAdd("q",1,[AutoPay]![NxtAPayDate]);
When I view the results the dates are the same - original dates, when I try to sort the results, I get a message box with the following: syntax error, incomplete query clause?
I cannot get the dates to change by using a specific date either
UPDATE AutoPay SET AutoPay.NxtAPayDate = #1/2/2014#;
I am trying to run a change an existing query in real time to allow date filtering for 4 different categories. I can get two of them to work. Calibration Date and Icepoint Date. But for The two calculated fields Calibration due date and Icepoint date I cannot get it to filter properly e.g. for 2013 dates it also includes 2014 dates and just does not work properly. I am thinking its due to it being a calculated field but don't have a clue how to fix it. see pasted code for calibration due date filter where Todate and Fromdate are the 2 dates used. I also have the on current code and the exit code to reset the query to its original status.
Public Sub SetDate1() 'Apply date filter and rebuild query in real time On Error GoTo Err_SetDate1 If IsNull(Me!ToDate) And IsNull(Me!FromDate) Then MsgBox ("Please Enter Date First"), vbExclamation GoTo Exit_SetDate1
