Minor edit causes query to stop working

[JemCain]

I finished writing a simple access database about 3 months ago but have come back to it to add extra functionality. However, some queries that I thought were working 3 months ago aren't working now. Here are some exmaples:

1. The following expression in a query gives a data type mismatch...

MonthName: MonthName(DatePart("m",[tblVisits]![VisitDate],2))

tblVisits!VisitDate has Date/Time data type. Also, if I remove the MonthName function, the DatePart function works on its own to give an integer month (e.g. 7 for July).

2. This expression also gives a data type mismatch...

WeekBeginning: DateAdd("d",-(Weekday([VisitDate])-2),[Visitdate])

VisitDate is the same field as in the previous example.

3. This is the one I really don't understand...

This query

Looks like this in datasheet view

But when I do a really simple edit (e.g. delete the M in MonthName and then retype it so it still says MonthName) then the datasheet view changes to this

The fact that the VisitDate field is involved in all of these exmaples makes me think that that's what's causing the problem; but I can't work out what. Any suggestions would be gratefully received!

Thanks.

 

[jdraw]

MS Access supports Month and MonthName functions. No need to deal with datepart.

To get monthName

try MonthName(Month(VisitDate),False) ' false is the default

 

[theDBguy]

Hi. It would be nice if you could post a sample db. Sorting the query by the name of the month would result in alphabetical order.

 

[CJ_London]

Also point out that Month is a reserved word as indicated by JDraw - using it as a column name may be causing an issue. But looks to me like you have remove the sort from the third column

 

[arnelgp]

MonthName is a function name, therefore should be avoided.
You acn also use Format to get the month name:

MoName: Format$(VisitDay, "mmmm")

It is more efficient since it is using only 1 function to get the month name.

 

[MarkK]

Look for a null value in the VisitDate field. Sort the source table ascending on that field. I bet you find a null value you aren't expecting could possibly be there.

 

[arnelgp]

Look for a null value in the VisitDate field

post#1:

Also, if I remove the MonthName function, the DatePart function works on its own to give an integer month (e.g. 7 for July).

 

[JemCain]

Thanks to everyone for your help. A combination of your advice worked and yes, there was also a null value! Really appreciate it.