Search results

Hi Mike - I'd break this down into smaller peices and try the NOT IN clauses. E.g. SELECT * FROM TableName WHERE TableName.ID NOT IN (SELECT TbaleName.ID FROM tableName WHERE <some condition>) Post back if I am not making sense. - g

Hi - Suppose I have a generic query that returns a group of records. Is there some way to generate a field containing the number sequence of each record? E.g. if there are 10 records, generate the numbers 1 through 10? Must be a simple way to do this, I'm just a bit dense today... Thanks, -g

Hi - See attached for one approach. I first created a query to duplicate the review date column. Then I ran this through the cross-tab query wizard to produce a query close to what you are looking for. The headers are dates, not "Review 1", "Review 2", etc. But with some more...

Actually, the code does not require AutoNumbers. The example of the function call is shown assuming a primary key called "ID". See the attached db. The form displays different records in Table1. If you press the command button, it will fill the fields at the bottom of the form with the...

I've tried to create some queries to do this, but I'm not having much success. You might be better off generating the values through some Visual Basic code. You would create a query in VBA to determine the correct date and then populate the fields with the appropriate values. - g

If you want to show students for the current selected class, you should be able to create a form and a subform. The main form would have the control to select the class and the subform can show students (in continous view). Be sure to set up a parent / child relationship on the class field.

Hi - What you are looking to do is probably best done with some VBA (visual basic). You can use the AfterUpdate property of the class type to launch a query. The query would look for the students in that class and populate a listbox. You can then select items from the listbox and click a...

You might be able to do it with a set of related queries - 1. Find the Date/Time of the current record. 2. Do a query to find the Max of the Date/Time field WHERE Date/Time is less than the current date. 3. Do a query to retrieve the specific record that matches the Max value found in Step...

Hi - Welcome! You need to link the form to a table. A better way to state this: You need to define a table (each of the fields and the type of data they will contain) and then create a form that is based on the table. If you create the table first, then use one of the form wizards, it...

Good deal!

Hi - welcome! Probably the simplest approach is to use the DatePart function to convert a booking date into a week number. E.g. WeekNum = DatePart ("ww",BookingDate) This will give you a week number for the year. You can build a query that has an expression to generate the WeekNum and then...

See the attached example. There is a query called qryParameterNameAge that allows you to enter a name and will display matching records. Note that the name must match exactly. The query shows both name and age. If you want age only, then go to design view and uncheck the "Show" box under...

Hi - The message you are getting is because of the referential integrity between the InvoiceDetails and Products tables. Each record in Invoice Details must have a valid product ID, but you may not have created it yet. You need to establish a valid product ID first and then select it (perhaps...

OpenFile or Shell seem to handle opening the file in an external program window, but what I would really like to do is first view the file on the form. - g

Hi - Trying to display PDF in a form. Ideally, I would like to link one or more PDF's to each record in a table and be able to view the PDF within the form. Opening the file via double-click would be a nice bonus. I've looked at a helpful post here...

Hi - The approach that comes to my mind is to enter both parameters as dates, and then ignore the year value. One way to do this is to use functions to check the serial # of the day. E.g. DATEPART ("y", DateVariable) See this thread for just a little more discussion...

Hi Tony - Welcome! I like to use the IN clause and NOT IN for this sorts of things: SELECT * FROM tblStores WHERE tblStores.ID NOT IN (SELECT tblReturns.ID FROM tblReturns WHERE tblReturns.Return = 9999) This first creates a sub-query with a list of all ID where Return = 9999. Then the main...

Congratulations, sounds like you are making real progress. Yes, the example did assume that field (FAC) was a number, not text. Sorry for the confusion. You can in fact use a text field, but have to add additional quote marks so that Access recognizes the comparison as text. E.g. instead of...

Check to see that you have a control for the FacID on the form. Me.FacID is referring to the form and needs to have a corresponding item.

First of all, creating a separate, distinct primary key is good general practice. If I have a table of books, I typically call the primary key (PK) BookID. If it is a table of employees, EmployeeID. This also helps when you have to join tables with related information. Secondly, if you have...