Form Data Transferring to Query and Other Fields Not Transferring

[dynamictiger]

I don't understand why straight forward things in this specific database are being so problematic. The only explanation I can think of is I haven't worked in 2016 much, with most of the work I have done in earlier versions of Access.

I have a form with three unbound fields. One combo box for the client and two unbound text boxes for dates.

I have a query to return records belonging to the clients, with the intent of getting only the records between the dates.

SQL as below at time of testing as there is an issue:

SELECT TOP 100 dbo_WaterBody.WBName, dbo_WaterBodyLogs.LogTime,
 dbo_ChemicalParameters.Parameter, dbo_ChemicalParameters.ShortCode, 
dbo_WaterBodyLogs.Result, dbo_Clients.Clientid, Left([LogTime],10) AS Expr1,
 Format([Forms]![Form1]![txtFro],"yyyy-mm-dd") AS Expr2, 
[Forms]![Form1]![txtDtTo] AS Expr3
FROM (((dbo_WaterBody INNER JOIN dbo_WaterBodyLogs ON dbo_WaterBody.WaterBodyID =
 dbo_WaterBodyLogs.WaterBodyID) INNER JOIN dbo_ChemicalParameters ON 
dbo_WaterBodyLogs.ChemicalParameterID = dbo_ChemicalParameters.ChemicalParameterID)
 INNER JOIN dbo_Site ON dbo_WaterBody.SiteID = dbo_Site.SiteID) 
INNER JOIN dbo_Clients ON dbo_Site.Clientid = dbo_Clients.Clientid

WHERE (((dbo_Clients.Clientid)=[Forms]![Form1]![cboClient]));

The issue is with the data on the form in field txtDTto. No matter what I do with this the field on the query returns a blank.


On the form the dates I am inputting are 1/1/19 and 20/1/19 I have formatted to match the data stored in SQL for the first date in Expr2, however have removed the formatting in Expr3 to simply get anything returned after finding it is returning nothing at all.

I am certain this is a follicle challenge...As in MS Access attempting to remove the remaining follicles I have in frustration.

I really dont have any idea how to diagnose this isssue any further. I even created a query to simply return the two dates and the query returns blank fields regardless of how the form is populated?

 

[June7]

Query doesn't show formatting of txtDTto.

See if Allen Browne guidance on formatting international dates helps http://allenbrowne.com/ser-36.html

 

[jdraw]

I suggest you post a "shrunk down" version of your database with just enough data to illustrate your issue- perhaps just an accdb with a few local tables.
Do you really need all the tables? Have you tried just searching for Client and a Date range to ensure the code works?You can always add more tables and complexity to the query - once you get something working.

 

[dynamictiger]

I suggest you post a "shrunk down" version of your database with just enough data to illustrate your issue- perhaps just an accdb with a few local tables.
Do you really need all the tables? Have you tried just searching for Client and a Date range to ensure the code works?You can always add more tables and complexity to the query - once you get something working.

Its all online SQL Linked data. And yes its working fine, its only the date field and only the DtTo that isn't working which is baffling to say the least, formatted, not formatted...just not working.

 

[dynamictiger]

Today for no reason at all it is working. I didn't change a thing. Left it and was working on other stuff now its working????????????

 

[The_Doc_Man]

dynamictiger, that's good news. However, be aware of an old adage: Problems that go away by themselves can come back by themselves. So keep a watch on that for a while.

Also, go back and revisit the "other stuff" to see if it might have affected the form or the form's .RecordSource.