Search results

Yeah, the bold part is what I was suggesting that you modify. Can you provide the relevant fields of each of the involved tables? e.g. Promo Count --------------- Promo No Number # of Demos Number Status String etc? I'll take another look and see if I can figure out...

I suspect that the HAVING (((Count(DemoOrder.Status))="E")); is part of the problem. Seems like this should just be DemoOrder.Status = "E". (This is part of the selection criteria, rather than the results returned.)

Hi Lisa - Probably the best way is to use DatePart() to extract the day of the year. E.g. DatePart("y", DateVariable) SELECT * FROM tblData WHERE ((DatePart ("y", tblData.DateValue) >= DatePart("y", StartDate) ) AND (DatePart ("y", tblData.DateValue) <= DatePart("y",EndDate) )) Not sure...

You actually can use spaces, but then you have to ensure that you delimit the name of the field with brackets. E.g. [Amt in Stock]. It can also cause problems down the line using Visual Basic or if you ever export to another database program. I just find it simpler to eliminate them and try to...

Hi - If you have entered a formula for a text box, then you can't change / override the value. It is possible to set some default values, but this is generally for things that are constant, rather than formulas. (E.g. put "NY" as the default value for State in the customer's address) It is...

Hi - and welcome! You are getting a message box because Access doesn't recognize the fields that you are using, so the program is asking you to supply the values. Check that you have typed all the field names correctly. If you are using spaces in the fieldnames, I recommend that you get rid...

Here is a general approach, though there are likely some details to work out: Set your subform in continous view. Then assign an event (probably DoubleClick) for the appropriate field. When you get a double click, you want to open the search form and let the user select a copy. If the user...

Hi - Pardon me for pushing my nose in. You can use the IN statement to identify all records that are in (or not in) a particular group. E.g. SELECT * FROM tblCopy WHERE tblCopy.CopyID NOT IN (Select CopyID FROM qryMaxOfDateRented) I haven't tested this, so you might have to tweak the...

See attached. I first tried to get the premium values for all the years listed in the tblYear table. The first step is qryCalcPremimumByYear, which figures out how many months of premiums. I assumed that a premium is due for the effective date of the contract, and that each contract only...

Thanks for the additional info.

Sure, doesn't matter whether the main form exists or not. Create your subform as you would do any other form, and save and close it. Then, open your main form, and use the toolbox to create a subform. You should be prompted to choose an existing form and just select the sub. - gromit

Hi - Can I take a step back and ask what years/dates are you interested in? E.g. are you looking from 2004 forward to the present? To 2006? 2007? The reason I ask is I am wondering whether it might be easier to look at specific months and then aggregate into a total [But depending on where...

You need to access the subform's properties - Select the subform in design view, click on the square at the upper left (has a black square dot). This should open the properties window for the subform. Look for the tab that says Format. On that tab there is an item called Default View...

hi - Not sure if I get all the ins and outs of your calculations, but here goes: You can use MONTH to figure out the number of months for a date. E.g. MONTH(#10/01/2004#) = 10 (for US date conventions). So 12 - MONTH([EffectiveDate]) + 1 = 3 <---- gets you your 3 MONTH([RenewalDate]) -...

Hi - I would first create an intermediate query to just sort the expenses into the appropriate groups. E.g. create a field in the query ExpenseType2 and use IIF to create the types of Rent Expense, Taxes and Other. You may be able to just use the crosstab directly off of this intermediate...

Normalizing the data means organizing your tables so that there is a meaningful structure and so that there is not unnecessary repetition. Basically, each table in a database should be independent and consist of just the information about the specific records in that table. For example, if...

Hi John - Wow, you have some seriously "non-normalized" tables there. If you have the option to re-do the table so that it is normalized, it would probably help with this and a lot of other potential problems. Is the use of the LIKE clause necessary? If the Employer Number is the whole...

Hi - Use the UCase function. E.g. CapsName = UCase(PatientName) - g

So far as I have seen, the only methods are (a) to declare a recordset or (b) use the "D" functions (DLOOKUP, DCOUNT, etc). You can reduce your code a line or two with Set mydb = CurrentDb set myRecSet = db.OpenRecordSet (mySQL) You might also look at this page...

Hi - Use a subform in datasheet view or continuous view. - gromit