Minor edit causes query to stop working (1 Viewer)

JemCain

New member
Local time
Today, 10:14
Joined
Jul 4, 2021
Messages
11
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

1625414624506.png


Looks like this in datasheet view

1625414676653.png


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

1625414777642.png

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

Super Moderator
Staff member
Local time
Today, 06:14
Joined
Jan 23, 2006
Messages
15,361
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

I’m here to help
Staff member
Local time
Today, 03:14
Joined
Oct 29, 2018
Messages
21,357
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

Super Moderator
Staff member
Local time
Today, 10:14
Joined
Feb 19, 2013
Messages
16,553
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:14
Joined
May 7, 2009
Messages
19,169
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

bit cruncher
Local time
Today, 03:14
Joined
Mar 17, 2004
Messages
8,178
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.
 

JemCain

New member
Local time
Today, 10:14
Joined
Jul 4, 2021
Messages
11
Thanks to everyone for your help. A combination of your advice worked and yes, there was also a null value! Really appreciate it.
 

Users who are viewing this thread

Top Bottom