Wsrtjtyk

I have no own "script syntax" for my application. It is designed for creating simple reports from a Microsoft Access Database using JET. Here is a simple example:

REQUIRES "Min number of away talks:" AS $iMinNumAwayTalks

LOOP FROM "Home Talks" NODATERANGE WHERE "Last Given" ISMOREOREQUAL "$Today"  SORTBY "Last Given"

    SHORTDATE_FIELD "Last Given"

    TAB =5>

    TEXT "Week: "

    VARIABLE_FIELD "Last Given" "$iWeekNo#CUSTOMDATE[%W]"

    TEXT "$iWeekNo"

    $iAwayTalkCount = 0

    LOOP FROM "Away Talks" NODATERANGE WHERE "Talk Date" ISMOREOREQUAL "$Today"  SORTBY "Talk Date"

        VARIABLE_FIELD "Talk Date" "$iAwayWeekNo#CUSTOMDATE[%W]"

        IF "$iAwayWeekNo" IS "$iWeekNo"

            $iAwayTalkCount = $iAwayTalkCount + 1

        END_IF

    END_LOOP

    TAB =10>

    TEXT "# Away: "

    TEXT "$iAwayTalkCount"

    TAB =15>

    TEXT "$iMinNumAwayTalks"

    TEXT " Exceed? "

    IF "$iAwayTalkCount" ISMORE "$iMinNumAwayTalks"

        TEXT "Yes"

    END_IF

    IF "$iAwayTalkCount" ISLESSOREQUAL "$iMinNumAwayTalks"

        TEXT "No"

    END_IF

    EOL

END_LOOP

It is basically looking at two tables to work out how many records have the same Week Number (beginning Monday) for the specified fields.

My question relates to this query I have used:

LOOP FROM "Away Talks" NODATERANGE WHERE "Talk Date" ISMOREOREQUAL "$Today"  SORTBY "Talk Date"

The downside with this query is that is is selecting all records greater than today and then I have to examine each record to see if the "Talk Date" field has the same week number.

Now, my script syntax supports raw SQL loops too. So is it possible using a raw Microsoft Access JET SQL query to:

Select all Away Talks where the Talk Date field is Week X (where Week is week starting Monday)?

I have tried to do a manual test in Access 2016 with the criteria:

SELECT [Away Talks].[Talk Date]

FROM [Away Talks]

WHERE ((DatePart('ww',[Talk Date],'vbMonday')=45));

But I have an error:

What am I doing wrong?

You provide vbMonday in your query as a string literal ('vbMonday'), instead of a numeric value.

Unfortunately you can't use the VBA constant vbMonday in a query, so you would have to use its numeric equivalent: 2

To find out this numeric value yourself you can enter ?vbMonday in the VBEs immediate window, or open the 'Object Browser' window for example by pressing F2 in the VBE.
There you could search for vbMonday and see it's numeric value at the bottom of the window.

Your criteria in the query design window is supplying a week number not a date so you need to create the week no field to apply the criteria to - try pasting your code into the SQL query editor

SELECT [Away Talks].[Talk Date]

FROM [Away Talks]

WHERE ((DatePart('ww',[Talk Date], 2)=45));