Search results

My reports are usually derived from queries. In the queries I typically use formulas that check for null or zeroes such as the following: iif(IsNull([table_name].[field_name]) or [table_name].[field_name]=0,0,[table_name].[field_name]) However, if its a formula I'll use the NZ formula...

Alright I figured it out. I had to create another query and pull in the first query as the source data, then use a combination of Dlookup and DMin. DLookUp("Base_Salary","q_survey_grade_analysis","AvgofSort_Order = " & DMin("avgofsort_order","q_survey_grade_analysis","AvgofSort_order > " &...

I think the best approach would be through a combination of a Dlookup formula and a sequential ordering column in the query. The dlookup would then have a criteria that is row number -1. Anyone know how to add a column in a query similar to a autonumber field in a table, where the numbers...

Thanks for the quick reply. While I understand the formula for percent change mathematically and in access across columns, I don't know the formula across records/rows. Sounds like DMax may be the answer. I'll investigate and see if I can post an example database with data. I'm using a total...

Hi, I'm trying to calculate the percentage change between records/rows. I've searched around the internet and found 2 possible examples, but they are very specific to the individual's database and I'm struggling to understand. I was hoping someone could help explain. Here are two examples...

Hi, I created a report that only has gridlines around the outside border of the entire report (rather than around each record). I want the grid line style on the last/bottom record to be solid so it boxes in all the records. When I change the "Gridline Style Bottom" for the records, each...

Thanks for the reply. However, the issue isn't a single calculation. It's when you calculate through multiple formulas and the program calculates the figures so exactly that it provides a different answer then you would get if you did it paper/pencil. The explanation for excel is here, however...

Nevermind...simple mistake. One too many coffees today...sorry.

Hi, I created a nicely formatted report, copied it and made a minor change to the new version. I then deleted the old version of the report, and the new version has lost all formatting. It looks like the basic report you get when you select a query and click Create -> Report. Is the new...

Hi, In Excel there is a common issue with precision vs. iteration when carrying calculations through multiple formulas. Excel has an option to adjust for this. I'm experiencing the same issue between Access' queries and reports. I have a query which calculates the percent increase between...

Hi, The data within my tables is formatted correctly and when I run a standard query on the data, it comes through the query with the same formatting. However, when I run a query that needs to total the values (whether it's sum or average) the values lose any formatting (and by total I mean...

Hi, I'm trying to create a report that has certain rows filled with a background color. In excel, the fill color is complete across the row. That is, there are no gaps between cells. In Access, each individual "cell" or field is filled with the color but inbetween each cell/field there is...

I want the user to be able to specify their search criteria if they want, but not be forced to. So if they selected a value from the drop down box, then I want that value used. However, if no value was selected, I want it to ignore that combo box. I tried using the value from the combo box as...

Hi, I'm attempting to filter my form with combo boxes. I've added the below code, but the if statement that checks if one of the combo boxes is null won't work. If the combo box is null or "", it's supposed to assign a wildcard to the variable. But it doesn't pass the test and goes straight to...

Hi, I'm trying to create a continuous form that allows users to filter. There are 2 combo boxes, "cbo_Field" and "cbo_Filter". I want "cbo_Field" to be a list of the available fields to apply the filter. I want "cbo_Filter" to be the value that is filtered (with wildcards at the front and...

Hi, I'm trying to run a saved import through VBA, add a new column called "Effective_Date", then update this field for all records to the effective date indicated by the user. It currently works for all records except 1 is always ommitted. Any help is appreciated. Private Sub...

Great, thanks!

Problem solved! I updated the index to ensure unique records for multiple fields that include the dates.

Hi, I have several queries that perform an audit on data. I want to create a form that has two buttons, multiple labels and textboxes. When the first button is pressed, it runs all queries and returns a count of the records within each query into the corresponding textbox. Then I want a a...

Hi, I'm attempting to create an append query that will add new records only if there isn't an equivalent record already existing. Typically I would add the existing table to the query, and only add new records if the same do not exist. In this case, the table is maintaining records over time...