Open Form with stLinkCriteria = "(Format([TimeGone],'dd/mm/yy')=" & Me!DateX (1 Viewer)

[bignose2]

Open Form with stLinkCriteria = "(Format([TimeGone],'dd/mm/yy')=" & Me!DateX

Is there a way to open a form and manipulate the Table side date

Basically I have a form & source table has the full date & time field
Invoices TimeGone = DateX 26-07-2018 13:01:47
Invoices1 DateX = 26-07-2018

I was to open the form using just the day part

I can filter with a query but just prefer to open in a few cases with simple openform command

stLinkCriteria = "(Format([TimeGone],'dd/mm/yy')=" & Me!DateX
DoCmd.OpenForm stDocname, , , stLinkCriteria

Missing "],)...." error
Just using [TimeGone] does not error but of course does not find anything.
I have tried dozens of different combinations of " ' brackets etc etc so above might be obviously poor but I am simply guess this is not possible on the table side.


The query is as below. This has the extra field of RegID but for testing I have ignored above.
SELECT Invoices.Key, Invoices.RegID, Format([TimeGone],"dd/mm/yy") AS TG
FROM Invoices
WHERE (((Invoices.RegID)=[Forms]![Invoices1]![RegID]) AND ((Format([TimeGone],"dd/mm/yy"))=Format([Forms]![Invoices1]![DateX],"dd/mm/yy")));

The two tables are Invoices & Invoices1, Invoices1 just had a date but I have formatted just to make sure correct way round for moment.

 

[pbaldy]

Re: Open Form with stLinkCriteria = "(Format([TimeGone],'dd/mm/yy')=" & Me!DateX

I would try:

stLinkCriteria = "[TimeGone] >= #" & Format(Me!DateX, "mm/dd/yy") & "# AND TimeGone < #" & Format(Me!DateX + 1, "mm/dd/yy") & "#"

 

[bignose2]

Re: Open Form with stLinkCriteria = "(Format([TimeGone],'dd/mm/yy')=" & Me!DateX

Thanks
That is a really good idea but bizarrely I have just managed to work it out, not sure why as I am sure I had tried this as with what seemed like hundreds of variations

SQLDate is an allen browne function that just made sure same format and I generally us a lot but went back to basics, I might tinker but thought best to say sorted.

stLinkCriteria = "Format([TimeGone],'dd/mm/yyyy')=" & SQLDate(Me!DateX)


So often I have spent many hours trying to find a solution, give up, post on here and within minutes find a solution myself

thanks anyway.

 

[pbaldy]

Re: Open Form with stLinkCriteria = "(Format([TimeGone],'dd/mm/yy')=" & Me!DateX

I would expect the same date format to be used, Allen's is putting it in the US format required by VBA. In any case, the reason I went the way I did is that using a function on the field will require that function be applied to every record in the table. Small table you probably won't notice a difference, large table you will.