searching and addition of field for given date

[waiwainoo]

Please help me out with this.

I have a table named Reservation, and two of its fields are Party_date and Booked_Tables.
What i want is when user input date and number of tables on form , the query should run and it should searcg and sum the total number of tables equal for this date in the table.

any suggesions will be highly appriciated.

Thanks

 

[ZmrAbdulla]

Have a look for DSum Function in the help file

________________________________________
Zameer Abdulla
Visit Me
There is only one perfect child in this world. Every mother has it.

 

[waiwainoo]

Actually i don't know the syntax very well.

so how can i write the SQL code for it ... there is a button on clicking which i want to have the sum of tables from the table for the given date which user has inputted.

plz help me with this one.

Thanks

 

[PHV]

Actually i don't know the syntax very well
When in VBE feel free to press the F1 key with the cursor inside the DSum word in your code.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[waiwainoo]

i tried the following just to add a values of one field first , but it is giving me errors.

Reservation is table name
Amount_of_people is field name

Dim s as integer
s = DSum("[Reservation]![Amount_of_People] = 's'")
Text53 = s

plz correct me

 

[PHV]

No F1 key on your keyboard ?
You may try something like this (in the AfterUpdate event procedure of your input date control):
Me![Text53] = DSum("Amount_of_People", "Reservation", "Party_date=#" & Format(Me![date control], "yyy-mm-dd") & "#")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

PHV,
You are much too helpfull. One small error that I am sure you left in the expression just to see if waiwainoo could find it.

Hint, it is in the format expression and pertains to the year...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Duane, you're a mind-reader ;-)

 

[waiwainoo]

Well PHV ... i don't know the syntax ... all i can find is that there are 3 y's instead of in the year

but it is still giving me error hilighting "Format" in this code
Me![Text53] = DSum("Amount_of_People", "Reservation", "Party_date=#" & Format(Me![date control], "yyy-mm-dd") & "#")

do you know what the problem is?

 

[PHV]

i don't know the syntax
Again, NO F1 KEY ON YOUR KEYBOARD ?

 

[waiwainoo]

I have used F1 , but i can't fix the error ... don't know whats wrong with this.

plz...

 

[waiwainoo]

when press F1 on Format ... it says Can't find project or library.

 

[dhookom]

First, change the name of Text53 to something like "txtTotalTables". This also assumes you have a text box to enter the date with a name of txtPartyDate.

Me![txtTotalTables] = DSum("BookedTables", "Reservation", "Party_date=#" & Format(Me![txtPartyDate], "yyyy-mm-dd") & "#")

This code could be in the after update event of txtPartyDate.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[waiwainoo]

well .. you gotta hate me for this.

But it says the Microsoft Jet can't find the table "Reservation" ... The table does exist and its the same spelling.

any suggestions?

Thanks

 

[dhookom]

You are out of luck if you have a table named Reservation and something like:
=DSum("1","Reservation")
doesn't work.

I would suggest searching the web on Access corruption and try to rebuild your database.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]