Replies

I had a mistake in the directions above:

Since it is a zip code and a set number of characters try this:

In a new column put =TEXT(A1,”000000″) and copy down.
(A1 is if your zip code is in column A row 1. Choose the column and row that your zip code is in)
Then highlight that column and
EDIT
copy
EDIT
Paste Special
Choose values

This removes the calculation. Delete the original column.

Thank you. I will try this tomorrow when I get to work. Let you know if it worked.

The spreadsheet I am updating is a spreadsheet that is set up with calculations and headings etc. I have different queries for different sections of the spreadsheet. When I connect to the Access queries for the different sections, I am overwriting the existing data. If the existing data is larger or smaller than than the query that the spreadsheet section is linked to, the data comes into the spreadsheet but the existing rows stay and are not removed. The data currently is less than the previous data that is in the spreadsheet. I am not sure what it does if there are more rows that are currently there. The extra rows have to be deleted manually unless there is something I am missing.

Thanks Hans. I did figure that out and I did change the criteria. The query works now.

I tried that but it said the query was too complicated. That is why I went with the prompts. It works in the main database but for some reason it doesn’t work in the small database that is a scaled down version of the main database. I run the query and the prompts work fine – it is just when I run the report that uses the query that I get the 7 prompts and then I get the same 7 prompts. If I Put something in one of the seven prompts, I have to put the same thing in the same prompt of the second set of prompts. If I just put something in the first prompt of the seven and then don’t put something in the first prompt of the second set of prompts, the report is not filtered. I redid the query several times but I can’t understand why it works when you just run the query and not work when you run the report.

Thanks for the answer. I didn’t think I was that daffy. Good thing I didn’t accuse anyone of tampering with the database…..

Thanks Hans.

The main database will be overwritten monthly. Will this break the links? Sorry for the dumb questions but I don’t want to set everything up only to miss something that won’t make this possible.

Thanks Hans – I will let you tomorrow if it works.

1) AugHealth = Y and SepHealth is blank – Loss
2) AugHealth = N and SepHealth is blank – Loss
3) AugHealth is blank and SepHealth = Y – Gain
4) AugHealth is blank and SepHealth = N – Loss
5) AugHealth and SepHealth are both blank – No change

What if there are blanks. I am home so I am not sure about the data. How would I account for blanks?

It was from an Excel file that I imported. I couldn’t even change the data type with the import wizard as it was dimmed out where I could change the data type. Why do they give you the option to change the data type if you can’t do it as one of the steps in the wizard?

I used the str function and it worked changing a number to a string. It was supposed to be text but came in as a number. I tried changing it to text in the table but it said I didn’t have enough memory. Thanks for your help.

I don’t have a choice about the data. This is data from a data cube . They want me to create reports from this data which for some reason the data cube can’t do right now so in the interim they need reports from this data until they get the data cube to work correctly. My part is basically done except they wanted some drill down report if possible. I created a Pivot table but it takes a while to open and when you try to scroll down the hour glass starts to run. I just thought if I could create a form that shows the high level and then show the detail too it might be easier to work with I guess I was thinking too much.

If you could see the whole database, it probably would make more sense. I am trying to do what a pivot table is doing. The y and n indicators tell the reps what products clients have at a high level for instance Y for health Y for vision N for dental N for UCCI etc – for that scenario there are say 49 clients. Then I wanted to show on the form the detail for the 49 clients – their name , number of contracts, number of members. Since that query is not updatable – could I bring it into another query and use that query or would that still be not undatable? If this is not possible to do, I will go with the pivot table.