Replies

Steve,

I was thinking along the lines of adding an image object “control” to your form, size it to the size of the form section and then make sure it’s “behind” everything else. When you add the image object, Access will ask for a file name (the picture you want to display). This can be a simple MS Paint bmp file with the color (or design!) you want — make it tiny, say 5×5 pixels — then with the object’s SizeMode property set to Stretch, it will fill the full extent of the object.

Another way to skin this cat, if you’re into VBA, is to have a Command Button “Print This Form” control on your form. In its OnClick event routine, change the background color property, use DoCmd.Printout to print the form, then change the color back. Something like:

Me.Section(“Detail”).BackColor = 16777215 ‘ White
DoCmd.PrintOut
Me.Section(“Detail”).BackColor = -2147483633 ‘ Original color

Of course, you probably want this button’s DisplayWhen property set to Screen Only.

Steven,

If you use an image control for your background, you can use its DisplayWhen property to determine when the object is visible. The choices are Screen Only, Print Only, and Both.

Hope this helps.

If you need just the years-old (not months, days, etc.) then try something like:

SELECT Names.Name, Names.DOB, Year(Date())-Year([DOB])+((Month(Date())<=Month([DOB])) And (Day(Date())<Day([DOB]))) AS Age
FROM [Names];

Easy? You decide.

Try something like

SELECT Name, DOB
FROM [Names]
WHERE Month([DOB])=10;

Of course, your DOB field must be defined as a Date/Time data type for this to work.

The source table (your address list?) should include a field for this fourth line. Normally it would be blank (null). Include this field on your report. If the value for the fourth line field is null it will just appear as blank. It sounds like this is what you want. You can also set the CanShrink property for this control on your report to Yes which will collapse this line when it’s Null. This shouldn’t affect the alignment of your labels if your columns and rows are set up correctly in Page Setup (e.g., as set up by the Labels report wizard). Also, as long as the CanShrink property of the Detail section is set to “No” your label height won’t vary.

Hope this helps.

Curious…

I took your Excel table and imported it into Access (I imported just the “Next” field and added a primary key ID field) and ran the query as shown above. The updated table had just the person’s name as the value of the “Next” field (without the hyphen nor occupation). This is what you want, right? I copied the SQL code from Access and pasted it directly into the Lounge reply so there shouldn’t be a typo. I am using Access 2000, but upon your reply I tried it in Access 97 and it worked there, too.

If all you want to keep in the table is the current inventory amount, then bind the form to this table (specify this table as the form’s record source). Include in the form a control (locked for editing) that shows the current inventory of the selected item (i.e., this control should be bound to the table value). Include an unbound control in the form for the quantity removed from inventory. In this control’s AfterUpdate event, update the bound current inventory control. Something like:

Private Sub txtQtyRemoved_AfterUpdate()

Me!txtCurrentInventory = Me!txtCurrentInventory – Me!txtQtyRemoved

End Sub

Hope this helps.

The easiest way to do this is with the InputBox function (don’t use a custom form at all). Something like:

Public Function dteGetDate(strPrompt As String) As Date

dteGetDate = CDate(InputBox(strPrompt))

End Function

Then your calling routine would have something like:

dtePurchaseDate = dteGetDate(“Enter the date of purchase:”)

But it sounds like you may want something a little fancier, using an actual form. This is a little trickier since the data in the form is available only when the form is open (unless the data is bound to a table). I’d suggest using an unbound form and assign the value of the date field (also unbound) to a global date variable in the control’s AfterUpdate event. Now, to “pause your code” while the form is open, you need to open it with the WindowMode set to acDialog. For example, something like:

DoCmd.OpenForm “DateForm”, , , , , acDialog

Code following this statement won’t execute until you close the form. When the form is closed, you can reference the entered date via the global date variable.

Hope this helps. If you need more specific details, holler. I’m sure someone will come to your rescue.

arage,

Make sure that the field you’re formatting as “mmmm” is a date field. If the field has “1” for January, “2” for February, etc. you’ll have problems (but in this case you could use the choose(n, string1, string2, …) function instead of the format() function).

jazman2001,

Okay, if I understand your dilemma correctly, you’re looking for an UPDATE query that will replace the NAME-OFFICE information in the field with just the NAME information. If this is correct, then something like

UPDATE [NameTable] SET [NameTable].Next = IIf(0<InStr(1,[Next],"-"),Left([Next],InStr(1,[Next],"-")-1),[Next]);

should work.

The iif statement first checks if there's a hyphen in the string (instr() returns zero if string2 not found in string1). If so, then it determines where the hyphen is located (again using the instr() function, since if string2 is found instr() returns the position in string1 where it's located). This position (minus 1) is used as the second argument of the left(string, length) function. If a hyphen is not found, then the whole name is retained.

Since this is an UPDATE query, the result of all this will replace the original "Next" field value, record-by-record.

Of course, if any of your names have hyphens in them (e.g., "ZETTA-JONES – ATTORNEY" then this scheme won't work — you'll just get "ZETTA").

Hope this helps.

willyboy,

I tried it, but I’ve still gotta debate it. Your suggestion works as long as the symbol you want is included in the font defined for the field. For example, the greek character “mu” (

If your number is always greater than or equal to zero you could use:

RoundedUpNumber = -int(-YourNumber)

It works for negative numbers, too, if you want the rounded number closer to zero (e.g., -1.1 rounds to -1 but not to -2). Note that for negative numbers, adding 1 as suggested by Mark and Douglas also gives you the rounded number closer to zero.

Tom

Adam,

True, the contents of a text field is data, but I guess what I was envisioning was a format option (in table definitions of text/memo fields as well as for controls in forms and reports) of “RTF” or “HTML” in addition to the existing options (colors, justification, etc. or nothing at all). The difference, of course, is that the data itself would have to include the formating information. True, this would make the data “messier”, but it would certainly jazz up the presentation of a text or memo field in tables, forms, and reports. A fair analogy is the body of an e-mail message.

At a minimum, it sure would be nice to have “in-line” formatting for label controls on forms and reports. For example, it shouldn’t be the nuisance that it is to embold one word in a form label.

Tom

My experience has been that out-of-the-box Access only allows one font for a given text/memo field. The trick may be to find a single font that has most of the normal alphanumerics you need plus the greek characters (replacing, for example, the international characters that are usually in the high end of the character map).

There maybe an add-in for Access out there that permits in-field formatting. Anybody know of one?

Maybe someday Access will catch up with Excel in this regard (which since Excel 97, I believe, has allowed in-cell formatting).

Tom

Don,

I think what Mark was saying was that your Order table also has to have a price field (to record the price copied over from the Price table when the order was created).

On your order form show the Order table price field but lock it from editing. In the After_Update event for the quantity field include something like Me!ctlPrice = Me!Qty * DLookup(“Price”, “Price Table”, “[ItemID] = ” & Me!ItemID). Note that Me!ctlPrice is bound to the Order table price field.

Hope this gets you going. If you need more detail, then holler — I’m sure someone will jump in and help!

Tom