My Visual Database — SQL search dates, From Sunday to Saturday, and count the dates

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (derek) — Sun, 12 Jul 2026 00:30:08 +0000

If you attach the latest version of your project I can have a quick look.
Derek.

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (jeffmtl@hotmail.com) — Sat, 11 Jul 2026 22:53:51 +0000

Hi Derek

I tried the simple option 1, checked line per line, double checked...compared your code with mine on Microsoft word to make sure they were identical (except for the reference to the databases columns). And still can't get it to work.

I even thought it could be the date format, so I added a date dd/mm/yyyy field from my initial date field that is yyyy/MM/dd. And still nothing.

At least I don't get an error message.

Any theories to help me out ?

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (derek) — Sat, 04 Jul 2026 11:48:28 +0000

Most of the calculated fields were because I was trying to second-guess where you might want to go with the project (and to give the functionality of Option 3 in the previous attachment).
But if the basic option does all you want, then you only need a single calculated field (see attached) which simplifies things a lot (and gives you the weekend off - LOL!!).
Derek.

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (jeffmtl@hotmail.com) — Sat, 04 Jul 2026 10:49:56 +0000

Thank you Derek

That is exactly what I was looking for. The basic version is perfect.

Now comes the fun part—I need to take some time to go through it piece by piece, understand what you did, and see how all the parts connect together. From what I can tell already, there's a real elegance in its clean simplicity.

Looks like I have a great weekend project ahead of me. Thanks again!

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (derek) — Sat, 04 Jul 2026 07:24:44 +0000

Hi Jeff,
Sorry for the delay - life keeps getting in the way
As others have indicated, there are quite a few different approaches you could take.
But as always, I take the easy road (!) and would just use calculated fields.
Have a look at the attachment that has 3 options
Option 1
Most straightforward - a summary grid showing closed files (by week) sits side by side with the main grid that holds all the individual details.
Option 2
A variation on the above - the summary grid is hidden until you click the relevant button (click it again to hide).
Option 3
Again, a summary grid and a main grid. This time, click any row in the summary grid and it filters the rows in the main grid (the gridlines also turn red as a visual reminder that a filter is active). Click another row in the summary grid to see a different set of filtered results. Clicking a second time on the filtered row clears all the filters, displays all the rows in the main grid and resets the gridlines back to black.
I created a 'calendar' table to hold 'weekstart' (Monday) and weekend' (Sunday) dates just to simplify things (and also to future-proof for any possible extra requirements). Note that the 'calendar' table was simply populated by copy and paste from Excel (it was quickest) rather than add extra code to the script.
I didn't do anything on the report side of things as I don't know if you want to report everything or just weekly summaries of closed files. But the raw data is all there so it should be pretty straightforward.
Hope this helps,
Derek.

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (jeffmtl@hotmail.com) — Fri, 03 Jul 2026 15:24:52 +0000

Here you are..sorry.

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (derek) — Fri, 03 Jul 2026 10:08:21 +0000

Nothing seems to have been uploaded.
Can you try again.

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (jeffmtl@hotmail.com) — Fri, 03 Jul 2026 01:32:24 +0000

Here you are...Note that the amount I put in for the grid is just random. I didnt count each accurately from my table.

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (derek) — Thu, 02 Jul 2026 23:37:32 +0000

Hi Jeff,
Can you re-load your attachment - nothing's come through.
Derek.

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (jeffmtl@hotmail.com) — Thu, 02 Jul 2026 22:33:26 +0000

Here is a mock of what I want to do, very simple. Note my creative EXCEL image for what I want the report to look like. I also would like to either print on excel or FAST REPORT.

tks

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (jeffmtl@hotmail.com) — Thu, 02 Jul 2026 22:08:45 +0000

I will attempt to make a small mock of the tables and forms. Please do not pay attention to my script put up earlier. Its what the internet suggested and I wasn't going anywhere with it, simply because it was deep in code and I couldnt make heads or tails of it.

Be right back !

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (sparrow) — Thu, 02 Jul 2026 21:41:03 +0000

Your solution contains several errors:
- The table's sqlValue property is intended to retrieve the ID. ( https://myvisualdatabase.com/help_en/sc … egrid.html )
- The SQLExecute function is intended to retrieve a single value in a query, such as a record ID, date, or quantity. To process multiple records, use the SQLQuery function.
- Everything you're trying to do can be accomplished using standard MVD tools, using a calculated field ( https://myvisualdatabase.com/help_en/Datatypes.html ) or by using the "SQL query" button if you prefer to write queries manually.. ( https://myvisualdatabase.com/help_en/SQLquery.html )

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (tcoton) — Thu, 02 Jul 2026 19:43:22 +0000

It's difficult to give a good answer without having access to your database but as far as what I see in the query is that the table you are querying does not match the description you give in the 2nd paragraph.

You might also need to update the sqlite.dll to get access to some functions which do not exist in the already old version of MyVisualDatabase.

Re: SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (derek) — Thu, 02 Jul 2026 09:33:43 +0000

Hi Jeff,
Can you attach your project?
Derek.

SQL search dates, From Sunday to Saturday, and count the dates

null@example.com (jeffmtl@hotmail.com) — Thu, 02 Jul 2026 04:06:30 +0000

Hi everybody

I am attempting to do an SQL SEARCH such that I have a list of dates indicating the closure of client files. The quary is to see how many files have been closed during week periods. So that you could see that on any given weeks the quantity closed.

FROM TO QTY
2026-01-01 2026-01-07 32
2026-01-08 2026-01-14 12

Best I could manage with the little program and help from the net was the below, but i keep getting blocked :

table : reference
column : Datecompletion
grid : reports.Gridperformance (and the property i think is sqlvalue)

Any help would be amazing cause im starting to loose my mind.

Tks.

reports.GridPerformance.sqlValue := SQLExecute(
'SELECT ' +
' date(DateCompletion, ''-'' || ((strftime(''%w'', DateCompletion)+6) % 7) || '' days'') AS week_monday, ' +
' COUNT(*) AS total_in_week ' +
'FROM referalDb ' +
'GROUP BY week_monday ' +
'ORDER BY week_monday;'
);