Query help

[karanvemuri]

Hi Robeen,

Below is the code which i put in

Private Sub Command112_Click()
Dim strCriteria As String

strCriteria = "[Agent] = '" & Me.[Agent] & "' "
strCriteria = strCriteria & "AND [Date] = #" & Date - 1 & "#"

DoCmd.OpenForm "DetailForm", , , strCriteria

Private Sub Form_AfterUpdate()

End Sub

An error is popin up, sayin Compile Error: expected End snub

any idea where did i go wrong?

[karanvemuri]

hi robeen, i have attached the DB for your reference.

[Robeen]

You have a missing 'End Sub' for your 'Private Sub Command112_Click()'.
I have highlighted it red.

Hi Robeen,

Below is the code which i put in

 
Private Sub Command112_Click()
Dim strCriteria As String
 
strCriteria = "[Agent] = '" & Me.[Agent] & "' "
strCriteria = strCriteria & "AND [Date] = #" & Date - 1 & "#"
 
DoCmd.OpenForm "DetailForm", , , strCriteria
 
End Sub
 
Private Sub Form_AfterUpdate()
 
End Sub

An error is popin up, sayin Compile Error: expected End snub

any idea where did i go wrong?

[karanvemuri]

I used the code,now a pop up comes showing Run time error 2465

Ms Access cant find the field '/1' referred to in your expression

[Robeen]

Can you put your Database here so I can look at it?

Or - have you had experience with debugging? It might be a good idea to put a break point in the code that you are executing when you get the error and then stepping through the code at run time to see which line is causing the error.

I ran the DoCmd that I posted so I know it works.
From the information that you have posted - I'm not sure where your error is being generated.

[karanvemuri]

hi robeen, i have attached the DB for your reference,thanks again

[Robeen]

What causes your error?

[Robeen]

Karan,
You have a Field in your Form [and Table] called 'Date'.
This is not a good idea because 'Date' is a reserved word in VBA [and VB].
When I execute 'Date -1' I don't get yesterday's date - I get the date previous to the date that is in the 'Date' field or your Form.
I was a little surprised at first - till I looked at the field name on your Form and then the field name in your Table.

[karanvemuri]

when i click the Search button on the Original form, with the search criteria given in the fields, the error shows up

[karanvemuri]

aah,so can i use Detaildate or somethin like tht instead of Date?

[Robeen]

Yes.

Before you make any changes though put this line of code in:

MsgBox Date

That should give you a message box that displays today's date.

You'll see that in your program - it will give you the date that is in the Date field of your Form.

[Robeen]

I do not get that Error when I run your Canada Tracker Form.

When I click the Search Button I get two results:
1. If there are one or more records in your Table that have a date that is one less than the date on the Form - those records will show up on your DetailForm.
2. If there are no records in your Table that have a date that is one less than the date on the Form - your DetailForm will open with no data showing.

So - it seems to me that the mechanics of your two Forms is working.
The Search button is opening the Detail Form
- BUT -
it is not opening it for 'Yesterday' - it is opening it for any records that have a date that is one less than the date on your Form.

[karanvemuri]

not sure why i am getting the error when i run the search though... i changed the field and form of Date to Detail date and also put in the Msgbox date...however the error 2345 still pops up highlightin the below code

strCriteria = "[Agent] = '" & Me.[Agent] & "' "

[Robeen]

Well . . . Neither of your Forms has a field called 'Agent'.

Change that line of code to this:

 
strCriteria = "[Agent Name] = '" & Me.[Agent Name] & "' "

Since both your forms have fields named 'Agent Name' - this should work.
I have it working in the copy that I pulled onto my pc.

When I run the Canada Tracker Form and go to a record that has a date of 9/19/2011 - and click Search - the DetailForm opens and it shows records for the Agent that is on the Canada Tracker Form.

Also - if you MUST have the DetailForm open in an Excel-like view [DatasheetView] - then try this:

DoCmd.OpenForm "DetailForm", acFormDS, , strCriteria

the 'acFormDS' - forces the data to be displayed in Datasheet View.

Let me know if it works for you now.

[karanvemuri]

changed the code to the below which you have given, another error pops up Runtime error 3075

Syntax error in Date in query expression '[Agent Name]= 'sreekiran Vemuri' AND [DetailDate]=#-1'