Query help

[Robeen]

Paste the code for your full subroutine here for me.
There is definitely a syntax error in it.

[karanvemuri]

Private Sub Command112_Click()
Dim strCriteria As String

MsgBox Date
strCriteria = "[Agent Name] = '" & Me.[Agent Name] & "' "
strCriteria = strCriteria & "AND [DetailDate] = #" & DetailDate - 1 & "#"
DoCmd.OpenForm "DetailForm", acFormDS, , strCriteria

End Sub

Private Sub Form_AfterUpdate()

End Sub

[Robeen]

Try putting your ssecond 'DetailDate' in box brackets:

 
strCriteria = strCriteria & "AND [DetailDate] = #" & [DetailDate] - 1 & "#"

Let me know if that works.

I have this working in the version that you send me [but I am still using your field names 'Date'].

[karanvemuri]

have put the box brackets but still no luck robeen, the same error is popin up sorry to trouble you on this

[Robeen]

I'm attaching what you sent me earlier - with my changes.

This is working on my machine here.

I am still using your 'Date' fields - so this will not give you Yesterday's date - only the date previous to the one that is on your main form.

Go to the record with 9/19/2011 and then click Search to see this working.

Let me know if it works.

[karanvemuri]

Robeen, Thanks a Mil!!!

Its running now!

You realy showed so much patience with me Thanks a lot for all your help and have a great week ahead..really appreciate, you are a Star

[Robeen]

I'm happy that I could help!

[karanvemuri]

Hi Robeen, just wondering if you could help me on this...whenever i give the search criteria on the orginal form, a new record is being created in the DB with the data given on the search fields...not sure why this is happening...as i have given access the option to chose the primary key...it might be creating a new record even though the data entered into the fields is for a search!

I have already posted a new thread on this,,but wasnt able to get much help,,,, awaiting ur reply

Thanks Again

[Robeen]

Same db that you posted earlier?

[Robeen]

I just opened your DailyData1.db that I had saved on my pc earlier.
I went to a record on the 'Canada Tracker' Form.
Then I clicked 'Search' - and I got DetailForm with the records for that Agent for the day prior to the date on the 'Canada Tracker' form.
I looked at the records on the DetailForm.
Then I closed the DetailForm.
Then I colsed the 'Canada Tracker' Form.
Then I opened the 'Canada Data' Table.
No new records had been added.

So . . . I am not able to reproduce the problem you are describing.

You might have to tell me step by step what you are doing to create your problem - so I can see it for myself and perhaps tell you why it is happening.

[karanvemuri]

Robeen, this has now been sorted out, thanks again for your time

[Robeen]

How did you fix it?