Search results

Welcome to the forum! First, you should not be storing this: 1112SJ0001. This is essentially a calculated value and calculated values are generally not stored in a table but rather calculated on the fly when needed. Of course, the components that are used to make the value need to be stored...

Could you provide the code that worked and the query that was generated from the code? From there we should be able to troubleshoot why it did not work when you save it as a standalone query. If you could post a zipped copy of the database that might be better.

You're welcome. Glad we could help out.

Since the permit number RA130113-04 is constructed, it would be a calculated value, so it would not be stored in your table but constructed and displayed on the fly when you need it. You will of course need to store the components that go into making the number. As to the code, you would use...

Just to clarify, if a person can be associated with more than 1 address over time (but not necessarily simultaneously), then I would alter my earlier structure as follows: tblAddress -pkAddressID primary key, autonumber -txtAddress -txtCity -txtState other fields pertinent to the address...

Typically you would record all of the permits that are created along with the type for each. Each created permit would be a record in the table. As you add new permit records, you would increment the permit number by 1 for each type. For example, let's say that an RA permit with the number of...

Welcome to the forum! Are the 5 permit fields of the PermitNum table related to one another or related to someone/something that requested a particular permit to do something?

The code would go something like this (air code, not tested): Dim mySQL as string IF DCount("*", "junctiontablename", "equipmentID=" & comboboxname.column(x) & " AND supplierID=" & comboboxname.column(y) )>1 THEN mySQL="Update tablename SET flagfield=-1 WHERE equipmentID=" &...

Do you mean that if you have duplicate records with the same EquipmentID you want to flag one of them? But, based on what you said earlier, you can have multiple records with the same EquipmentID (but different suppliers). Do you really mean that you want to flag records that have the same...

The correct format for the command is as follows: me.[date closed]=now() (me. is a shorthand notation for the current form assuming that the combo box and the date closed controls are on the same form)

Assuming that you have a date field in your table, you would just add criteria to the DELETE query as follows WHERE yourdatefield between date() and dateadd("d",-30,date())

Since you have not provided your current table structure, this is how I would structure the tables tblAddress -pkAddressID primary key, autonumber -txtAddress -txtCity -txtState other fields pertinent to the address tblPeople -pkPeopleID primary key, autonumber -fkAddressID foreign key to...

To start, create a query that finds those accounts for which you have received overdue notices that have occurred today or within the four preceeding days SELECT [acct#] FROM overduetable WHERE datediff("d",entrydate,date())<=5 BTW, it is best not to have spaces or special characters in your...

If there are multiple people associated with an address than that describes a one-to-many relationship which by normalization rules requires the people to be put into a separate but related table. The next question, is can a person be associated with more than one address?

Since you are using the find first method, the code will always take you to the first occurence of the equipmentID that it runs into. Since you have multiple suppliers for an equipmentID, the equipmentID is not unique. Could you use the primary key of the junction table instead of equipmentID...

You're welcome.

Allen Browne discusses how to handle the different internation date formats in Access on his website

I started with Access VBA Programming For Dummies by Alan Simpson several years ago. I believe that book was written based on Access 2003. I assume that there are books for the newer versions of Access.

You cannot call a result from VBA and get it into a report directly. You have three options. First, is to use the query you built in the code and assign it to the record source for the report. If you still have to join the created query to another table/query then you can do that in code as...

You're welcome. Glad we could help out.