Replies

Try moving the code to the On Format event.

Try moving the code to the On Format event.

Code I was using is =”On “& [Date] &”, we had a snowstorm.”

Change the control source for this textbox to:

=”On “& Format([Date], “Long Date”) &”, we had a snowstorm.”

Note that Date is a reserved word in Access and not the best choice of name for a date field. Also, how Long Date displays is entirely dependent on the settings for Long Date in Regional Settings in Control Panels. You could also specify exact formatting rather than Long Date, e.g. “mmm d, yyyy” which would give you January 2, 2010 or “dddd, mmm d yyyy” which will give you Saturday, January 2, 2010 as you indicate.

Code I was using is =”On “& [Date] &”, we had a snowstorm.”

Change the control source for this textbox to:

=”On “& Format([Date], “Long Date”) &”, we had a snowstorm.”

Note that Date is a reserved word in Access and not the best choice of name for a date field. Also, how Long Date displays is entirely dependent on the settings for Long Date in Regional Settings in Control Panels. You could also specify exact formatting rather than Long Date, e.g. “mmm d, yyyy” which would give you January 2, 2010 or “dddd, mmm d yyyy” which will give you Saturday, January 2, 2010 as you indicate.

Code I was using is =”On “& [Date] &”, we had a snowstorm.”

Change the control source for this textbox to:

=”On “& Format([Date], “Long Date”) &”, we had a snowstorm.”

Note that Date is a reserved word in Access and not the best choice of name for a date field. Also, how Long Date displays is entirely dependent on the settings for Long Date in Regional Settings in Control Panels. You could also specify exact formatting rather than Long Date, e.g. “mmm d, yyyy” which would give you January 2, 2010 or “dddd, mmm d yyyy” which will give you Saturday, January 2, 2010 as you indicate.

Code I was using is =”On “& [Date] &”, we had a snowstorm.”

Change the control source for this textbox to:

=”On “& Format([Date], “Long Date”) &”, we had a snowstorm.”

Note that Date is a reserved word in Access and not the best choice of name for a date field. Also, how Long Date displays is entirely dependent on the settings for Long Date in Regional Settings in Control Panels. You could also specify exact formatting rather than Long Date, e.g. “mmm d, yyyy” which would give you January 2, 2010 or “dddd, mmm d yyyy” which will give you Saturday, January 2, 2010 as you indicate.

Code I was using is =”On “& [Date] &”, we had a snowstorm.”

Change the control source for this textbox to:

=”On “& Format([Date], “Long Date”) &”, we had a snowstorm.”

Note that Date is a reserved word in Access and not the best choice of name for a date field. Also, how Long Date displays is entirely dependent on the settings for Long Date in Regional Settings in Control Panels. You could also specify exact formatting rather than Long Date, e.g. “mmm d, yyyy” which would give you January 2, 2010 or “dddd, mmm d yyyy” which will give you Saturday, January 2, 2010 as you indicate.

Code I was using is =”On “& [Date] &”, we had a snowstorm.”

Change the control source for this textbox to:

=”On “& Format([Date], “Long Date”) &”, we had a snowstorm.”

Note that Date is a reserved word in Access and not the best choice of name for a date field. Also, how Long Date displays is entirely dependent on the settings for Long Date in Regional Settings in Control Panels. You could also specify exact formatting rather than Long Date, e.g. “mmm d, yyyy” which would give you January 2, 2010 or “dddd, mmm d yyyy” which will give you Saturday, January 2, 2010 as you indicate.

Code I was using is =”On “& [Date] &”, we had a snowstorm.”

Change the control source for this textbox to:

=”On “& Format([Date], “Long Date”) &”, we had a snowstorm.”

Note that Date is a reserved word in Access and not the best choice of name for a date field. Also, how Long Date displays is entirely dependent on the settings for Long Date in Regional Settings in Control Panels. You could also specify exact formatting rather than Long Date, e.g. “mmm d, yyyy” which would give you January 2, 2010 or “dddd, mmm d yyyy” which will give you Saturday, January 2, 2010 as you indicate.

Code I was using is =”On “& [Date] &”, we had a snowstorm.”

Change the control source for this textbox to:

=”On “& Format([Date], “Long Date”) &”, we had a snowstorm.”

Note that Date is a reserved word in Access and not the best choice of name for a date field. Also, how Long Date displays is entirely dependent on the settings for Long Date in Regional Settings in Control Panels. You could also specify exact formatting rather than Long Date, e.g. “mmm d, yyyy” which would give you January 2, 2010 or “dddd, mmm d yyyy” which will give you Saturday, January 2, 2010 as you indicate.

Thanks for the input. I modified the format for the two controls and set the decimal places to 0. But that doesn’t seem to carry through to the PivotTable display as the display is of a count of the one control and a sum on the other. Also tried setting the format in the underlying query which didn’t work either.

Without seeing a sample version of what you are trying to do or a screen shot, I can’t add a lot more except to say that you can right click on the field in the pivot table and change formatting there as well. Otherwise, you would have to post a zipped copy of the database with confidential info removed.

Pivot Tables are based on forms and are essentially a different view of the form. If you set the formatting you need for the textbox in question it should appear in the right format for the pivot table. Try setting the format for the textbox to fixed and the number of decimals to 0.

I think you answered your own question.
A boolean value by default is either TRUE or FALSE. Not NULL.
You are using an Integer with 3 values.
Either change the parameter to be an integer and adjust query.
Realistically you need the filter that gets used by the query to only have a filter if true or false and no filter otherwise.
The function is boolean so it cannot retyrn a NULL.
What is the query that receives the function result.

I was going at it from the wrong direction. Rather than trying to use a saved query and pass “True or False” to it as a parameter, I am running the query in VBA with Docmd.RunSQL and appending the appropriate WHERE condition via my function.
Thanks for the reply.

Thanks Hans, that is exactly what I needed.

Thanks Wendell and Andrew for the suggestion and syntax about Save as Text. I ran this just now and the current corruption seems to have gone away. I will still probably rebuild the forms, but for the time being, I think I have something I can work with.