Replies

Thanks for your reply. This confirms what another person told me. I guess I’ll have to rely more on the sorting and grouping capabilities in the report itself.

I have a query with data sorted by address. When I create and run a report based on the query, the data sorts properly. However, if I add the Count() function or a Sum() function to the Report footer, the data no longer sorts. It prints in the table order. What gives?

A sample set of data is attached.

I’ve looked for a solution and can’t find it. Any help you can provide would be great.

Thanks very much for the explanation. I’ve really learned lots.

Jeff

I’ve been examining what you sent and have a few questions. 1) When I enter a new record using the data entry form you modifed (such as 1234 Broadway, dated 12/29/2006) it appears to work properly, but when I open the table DRB Exemptions, the street information is not stored in the table. Yet when re-open the form, the street name information displays as I entered it. How does that magic happen? 2) You stated that qryUpdate “fills in the new SitusID field in DRB Exemption Log where available”, but when I examine the query, it appears to only update the SitusID field in qryDRBExemptions. Am I missing something? 3) When I run qryMissing, I see a listing of several streets that presumably don’t appear in Street Names. Yet when I look at the Street Names table, those street names seem to be there correctly. Any idea what’s happening?

Thanks very much again.

Jeff

Thanks very much for taking the time to help a rookie.

Jeff

Thank you very much for taking the time to reply to my post. Unfortunately, I’m not experienced enough with Access to understand your proposed solution.

I linked to the Street Names table. I created a field in DRB Exemptions of the same type (Number, Float) as the IDNum field in Street Names, then I created a query linking the tables based on the common field, but the query produced no results when I tried to display all the DRB Exemptions records. Probably because the StrNum field in DRB Exemptions that I added to the table has no values in it. Do I need to populate that field in all the existing records so that there’s a valid relationship between the two tables?

I tried to create a combo box bound to the number field, but I can’t figure out how to bind it to the number field while simultaneously setting the Row Source to the Street Names table.

If this would all take too much time and trouble to explain, I understand. I’ll bug my IS staff. I was hoping this was easy enough for me to do.

Jeff

You’re revised code worked perfectly. I tested it on a larger batch of data, and everything worked. It handled nulls and data that lacked commas just fine.

Thanks again to you and to Mark for taking the time and having the patience to help me. I really appreciate it.

You’re revised code worked perfectly. I tested it on a larger batch of data, and everything worked. It handled nulls and data that lacked commas just fine.

Thanks again to you and to Mark for taking the time and having the patience to help me. I really appreciate it.

Unfortunately, your code did not deal well with null values and names that had no commas. I know you advised me to use the Nz function in the query, but I don’t know what that is or how to use it.

Unfortunately, your code did not deal well with null values and names that had no commas. I know you advised me to use the Nz function in the query, but I don’t know what that is or how to use it.

Thanks very much for your help. I’ll give your code a try.

Thanks very much for your help. I’ll give your code a try.

Thanks very much for your code example. Would all this be in one module? Is that module of VBA what is known as a user defined function?

Thanks very much for your code example. Would all this be in one module? Is that module of VBA what is known as a user defined function?

Is such special handling something best left to an accomplished programmer (something I’m not) or are the solutions simple enough that I can understand and implement them? BTW, I have to deal with close to 40,000 records, of which a few hundred change each month. There’s no easy way to know each month which records may have these unique naming characteristics.