Query misfiring and giving wrong results

[unclemeat]

i have a query which is supposed to be bringing up all the items that have sold since a certain date.
i have previously used a form to nominate the date as there are a few queries using the same date.
it has just started bringing in entries that are not valid and i have no idea why.
if i dont put open the form and put the date there it seems to work fine.
but as soon as i put the date in the form, it no longer asks me for a date BUT also finds many more stock items which are seemingly not valid.
i have included the query here - it should select all books in stock that have been updated after a certain date. like i said if i get asked for the date and put it in it brings up approx 150 titles which is correct. if i put the date in the form it brings up 6000.


thx for any help.
rob

[Welshgasman]

Nothing attached?

Better to upload the DB?

However what date format do you use? MS expects mm/dd/yyyy or yyyy-mm-dd
If used in SQL string also needs to be qualified with # character.

[unclemeat]

i will try and reduce the size to upload.

[Welshgasman]

Compact and zip a copy of your db.
If still too large, just upload the minimum needed to see the issue.

[unclemeat]

hi there - hope this is usable - i deleted a heap of forms reports etc to get it even close. think i left the stuff behind i need help with.

[Bob Fitz]

hi there - hope this is usable - i deleted a heap of forms reports etc to get it even close. think i left the stuff behind i need help with.

It might be helpful for you to tell us which of the queries and which of the forms we should be looking at

[mike60smart]

Hi

You need to normalise your table "shopify"

All of the following Fields should be Records in a Related Table

Reggae

World Music

Women

Metal

Audiobooks

Country

Folk

Jazz

Indigenous

Australian

Collect

Fiction

Non Fiction

WEBKeywords

War

Sport

Politics

Crime

Signed

Cookery

Self Help

SciFi

Romance

Prize

Biography

Nature

Music

History

Historical Fiction

Comic

DIY

Childrens

Art

Classics

Health

Transport

Humour

Poetry

Science

Teen

Travel

Spirituality

Manuals

Horror

LGBTQ

Gardening

Documentaries

Movies

HIp Hop

Box Sets

Alternative

Dance

Soundtrack

Blues

Punk

Compilation

Christmas

FNQ

Classical

TV

Magazines

[Welshgasman]

Yes, it needs work. That is why you have so many queries, one for CDs one for books. You should have one query and then specify which product you want to use.
Regardless, we still need the query and instructions on how to recreate the issue?, Also what should we expect to be correct.

Remember, you know this DB intimately, we do not.

[unclemeat]

apologies. so the query is ebaycdqry.
if you open it and put 1/4/2023 when prompted it gives 17 results.
if you open the reports f form and put 1/4/2023 in the date field it gives 250 results.
the 17 results is correct - im not sure where the other records are coming from.
the ebaycdqry should be looking for the date in reports f form (i think).

[unclemeat]

do you mean set up a table with say music styles and then link any relevant records to it ? i know thats how its done with condition but its only 1 value whereas some cds fit into multiple styles.

[mike60smart]

The fields I indicated should be in a table called tblStyles

tblStyles
-StyleID PK
-Style
You then need to create an additional table named tblMusicStyles

tblMusicStyles
-MusicStyleID - PK
-ShopifyID - FK
-StyleID - FK (Linked to PK tblMusicStyles)

You can then add a number of related Styles

[Bob Fitz]

In the query, replace: >[Forms]![reports f]![date]
with: >Format([Forms]![reports f]![date],"mm\/dd\/yyyy")

Read what Allen Browne says about international dates in Access. See: http://allenbrowne.com/ser-36.html

BTW I would recommend you to redesign your tables which are not "Normalized".

[Welshgasman]

In the query, replace: >[Forms]![reports f]![date]
with: >Format([Forms]![reports f]![date],"mm\/dd\/yyyy")

Read what Allen Browne says about international dates in Access. See: http://allenbrowne.com/ser-36.html

BTW I would recommend you to redesign your tables which are not "Normalized".

As I suspected.

I used this constant in my DBs for such a reason.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

as I could never remember the exact syntax. The I just used that as the format parameter.

[unclemeat]

hi bob
this is still same problem i think.
if you open reports f and put in 4/4/2023 and open the ebaybookqry query (its pretty much the same as the cd one except for books) (with the new code under recordcreated) you get 283 records.
if you then close reports f and run the query, it prompts for a date and so you put in 4/4/2023 you get 9 records (which i think is right). not sure why the other records are coming in - there are heaps that were last changed on 25/02/2023, 6/03/2023 etc which are obviously not greater than 4/4/2023.
just not understanding why they are being selected by the query.
thx
rob

[unclemeat]

where would you put that code in a database ?