Query does not output a simple string correctly 1

[danwand]

Hi there,

I have a perplexing problem with a field in a query that should be rather simple:

The field causing trouble is this:

Dates: IIf(IsNull([HSEndDate]),Format([HSStartDate],"ddd dd-mmm-yy"),"From " & Format([HSStartDate],"ddd dd-mmm-yy") & " To " & Format([HSEndDate],"ddd dd-mmm-yy" & " (" & (DateDiff("d",[HSStartDate],[HSEndDate])+1) & " days)"))

When running this query all those records that have only a start date the results are as they should be, however those that have and end date produce the following result:

From Sat 01-Apr-06 To Sun 02-Apr-06 (2 2a920)
From Mon 03-Apr-06 To Fri 07-Apr-06 (5 7a970)
From Tue 13-Mar-07 To Thu 15-Mar-07 (3 15a740)

So the count is correct but for some reason the string "days" is outputted as above e.g. 2a920 or 7a970.

If i take the string "days" away and replace it with a closing bracket i.e. [HSEndDate])+1) & ")")), the results are fine.

I don't understand why the query doesn't just append the word days.

Any help would be grately appreciated.

Thanks
Dan

 

[HarleyQuinn]

I'd move one of your final closing brackets to the Format of your HSEndDate e.g.

Dates: IIf(IsNull([HSEndDate]),Format([HSStartDate],"ddd dd-mmm-yy"),"From " & Format([HSStartDate],"ddd dd-mmm-yy") & " To " & Format([HSEndDate],"ddd dd-mmm-yy") & " (" & (DateDiff("d",[HSStartDate],[HSEndDate])+1) & " days)")

The reason I would suggest this is I'm assuming that it's trying to use

"ddd dd-mmm-yy" & " (" & (DateDiff("d",[HSStartDate],[HSEndDate])+1) & " days)")

as the format for the later part of your query.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[danwand]

Perfect.

You are a star and a star for you.

Thanks very much.

 

[HarleyQuinn]

Glad I could help

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.