Making a cash balance

[Marinus]

Dear Friends, for days I try to work out a way to make a form that will allow for the input of a date(textbox with date picker), a amount of euro's in cash Textbox and then a query to calculate the result of that day from the table, then a textbox that will deduct the query result from amount of euro's and that should display the money in the till. At a certain moment I had this working without the date selection. It showed just the total of items purchased. After that and I can't remember what it was, all the forms that I try to make come up as blanks.. No fields visible nothing.. Could anyone point me in the right direction please??. For the date I used Forms!frmCash!txt_D_date.

The query is here;

SELECT DISTINCTROW tblDocket.ID, TblDocketBuy.ID, TblDocketBuy.F_Weight, TblDocketBuy.E_Weight, TblDocketBuy.B_Price, TblDocketBuy.Dirt, Sum(((([F_Weight]-[E_Weight])*((100-[TblDocketBuy.Dirt])/100))*[B_Price])/1000) AS DayTotal, tblDocket.D_Date
FROM TblDocketBuy INNER JOIN tblDocket ON TblDocketBuy.[DocID] = tblDocket.[Docket_Number]
GROUP BY tblDocket.ID, TblDocketBuy.ID, TblDocketBuy.F_Weight, TblDocketBuy.E_Weight, TblDocketBuy.B_Price, TblDocketBuy.Dirt, tblDocket.D_Date, tblDocket.D_Date
HAVING (((tblDocket.D_Date)=[Forms]![frmCash]![TxtD_date]));

I also tried calculating directly in a textbox that is what worked for the whole table.. Sorry just lost the plot..

 

[SpentGeezer]

Greetings,

Have you checked that tblDocket.D_Date and
[Forms]![frmCash]![TxtD_date] are in the same format?

One of them might be date/time or US format.
Put this into the event to check them:

msgbox tblDocket.D_Date
msgbox [Forms]![frmCash]![TxtD_date]

 

[SpentGeezer]

I also recall having to convert to US format and put # on either side of the date in an SQL statement....

 

[Marinus]

I also recall having to convert to US format and put # on either side of the date in an SQL statement....

Thanks for your help, I don't see where I can do this as I use the builder, my knowledge is low so to say. I have it working except changing the day on the form does not do anything, I get a full total in my TxtTurnover field what ever date I choose. When I take the Forms!frmMakeCase!TxtDate out of the query it makes no difference..

I will attach a JPG to make things clear..

 

[DCrake]

Have you tried refreshing the query

 

[Marinus]

Have you tried refreshing the query

Did try and now the form doesn't work anymore But the TxtTurnover box start blinking and showing error on and off.. I must be on the wrong way..
Wasn't stable from the beginning so I must make a mistake somewhere..
The joys of learning ..

 

[vbaInet]

Requerythe form.

The Day Turnover textbox has invalid syntax in the 3rd argument of the DSum() funtion:

That is:

=DSum("Correct", "Correct", "Wrong!")

 

[Marinus]

Requerythe form.

The Day Turnover textbox has invalid syntax in the 3rd argument of the DSum() funtion:

That is:

=DSum("Correct", "Correct", "Wrong!")

Got the form back by removing the 3th argument, still get the total of all purchases, and sorry to ask but queries are really new to me, how do I get this form to show the Turnover amount for the given day with the date picker and where would be the best place to re-query..

Sorry guys... for asking...

 

[vbaInet]

Into the Day Turnover textbox?

You still need the 3rd argument. That is the argument that filters your result. Format:

=DSum("[[COLOR=Red]Field[/COLOR]]", "[COLOR=Red]QueryOrTable[/COLOR]", "[[COLOR=Red]DateField[/COLOR]] = " & [[COLOR=Red]DateControlOnForm[/COLOR]])

Amend all the bits in red.

 

[Marinus]

Into the Day Turnover textbox?

You still need the 3rd argument. That is the argument that filters your result. Format:

=DSum("[[COLOR=Red]Field[/COLOR]]", "[COLOR=Red]QueryOrTable[/COLOR]", "[[COLOR=Red]DateField[/COLOR]] = " & [[COLOR=Red]DateControlOnForm[/COLOR]])

Amend all the bits in red.

Have done the third argument;

=DSum("[DayTotal]","[QryDayPurchase]","[QryDayPurchase]![D_Date] =  " & [Forms!frmMakeCash!TxtDate])

Might not have been the right thing, but this I understood, Tried to do the # # in and out of the brackets get #Name? so not yet there.

Should have sticked to being a chef..

 

[vbaInet]

I wrote [DateControlOnForm], not [PathToDateControlOnForm]. You've done the latter not the former.

 

[Marinus]

I wrote [DateControlOnForm], not [PathToDateControlOnForm]. You've done the latter not the former.

Thanks for that, So I have changed this to;

=DSum("[DayTotal]","[QryDayPurchase]","[TblDocket.D_Date] =" & [TxtDate])

also tried;

=DSum("[DayTotal]","[QryDayPurchase]","[D_Date] =" & [TxtDate])

When I load the form I get error# in the TxtTurnover textbox, probably because no date exist in the TxtDate textbox yet, then when a start cash amount and date is entered the TxtTurnover textbox goes blank..

Have tried all varieties but don't get it..
I tried to run the query with the where statement at the D_Date column, then the query is blank, when I run it with group by it stays blank. When I remove the Forms!frmMakeCash!TxtDate, the query returns the full table..

Tried placing the # TxtDate #, errors fly around my ears..

Searched the board, tried every variety.. Can only think that it is because the D_Date comes from another table but I am not sure about that so to try I have also included the [tblDocket.D_Date] in the last statement.. Boy am I missing the point here...

 

[vbaInet]

Tried placing the # TxtDate #, errors fly around my ears..

I hope you swat them

In the example I gave notice that the Query name parameter isn't surrounded by square brackets ([])

=DSum("[DayTotal]","[QryDayPurchase]","[TblDocket.D_Date] =" & [TxtDate])

 

[Marinus]

I hope you swat them

In the example I gave notice that the Query name parameter isn't surrounded by square brackets ([])

Found that Guinness and Access don't go very well together.. Killed them all, removed the brackets, no more errors, but TxtTurnover stays Blank.. I am in doubt what to put in the Query, Where or Group by at the D_Date column..

 

[vbaInet]

Try these:

=DSum("[DayTotal]", "[QryDayPurchase]", "[D_Date] = " & [TxtDate])

=DSum("[DayTotal]", "[QryDayPurchase]", "[D_Date] = #" & [TxtDate] & "#")

=DSum("[DayTotal]", "[QryDayPurchase]", "[D_Date] = " & [[COLOR=Red]NameOfDate[COLOR=Blue]Field[/COLOR]OnForm[/COLOR]])

=DSum("[DayTotal]", "[QryDayPurchase]", "[D_Date] = #" & [[COLOR=Red]NameOfDate[COLOR=Blue]Field[/COLOR]OnForm[/COLOR]] & "#")

I would advise for next time that when you're given some syntax for a function, copy and paste it somewhere and amend the field names. Right from the start your syntax just wasn't what I wrote.

 

[Marinus]

Try these:

=DSum("[DayTotal]", "[QryDayPurchase]", "[D_Date] = " & [TxtDate])

=DSum("[DayTotal]", "[QryDayPurchase]", "[D_Date] = #" & [TxtDate] & "#")

=DSum("[DayTotal]", "[QryDayPurchase]", "[D_Date] = " & [[COLOR=Red]NameOfDate[COLOR=Blue]Field[/COLOR]OnForm[/COLOR]])

=DSum("[DayTotal]", "[QryDayPurchase]", "[D_Date] = #" & [[COLOR=Red]NameOfDate[COLOR=Blue]Field[/COLOR]OnForm[/COLOR]] & "#")

I would advise for next time that when you're given some syntax for a function, copy and paste it somewhere and amend the field names. Right from the start your syntax just wasn't what I wrote.

Hi vbaInet, thank you for this lesson, usually I would do your suggestion but in my frustration I just tried about everything on the internet. Take into consideration a 17 year old daughter with a baby and I am sure you can imagine what that does to the mind.. I can a sure you that doesn't help.
At his moment I am trying the following that seems to give the right result.

=DSum("[DayTotal]","[QryDayPurchase]","[D_Date] = #" & [D_Date] & "#")

Instead of a textbox, I did put the field on the form. One problems stays that is not consistent because I miss the correct procedure for a requery on that field, sometimes when I change fields to put in a amount of cash the date changes as well. I can only imagine 2 scenarios where I go wrong. The tab order and the place to requery.. any help much appreciated..
You are right with the remark to paste solutions before trying them.. At this moment however I try to do two things at the same time and the world is catching up with me.. Sorry for that..

 

[vbaInet]

One problems stays that is not consistent because I miss the correct procedure for a requery on that field, sometimes when I change fields to put in a amount of cash the date changes as well.

I don't understand what you're asking here. Is this question related to your original post?

If the Date is changing then I would imagine you have code in the After Update event of one of your controls that causes the change.

 

[Marinus]

Try these:

=DSum("[DayTotal]", "[QryDayPurchase]", "[D_Date] = " & [TxtDate])

=DSum("[DayTotal]", "[QryDayPurchase]", "[D_Date] = #" & [TxtDate] & "#")

=DSum("[DayTotal]", "[QryDayPurchase]", "[D_Date] = " & [[COLOR=Red]NameOfDate[COLOR=Blue]Field[/COLOR]OnForm[/COLOR]])

=DSum("[DayTotal]", "[QryDayPurchase]", "[D_Date] = #" & [[COLOR=Red]NameOfDate[COLOR=Blue]Field[/COLOR]OnForm[/COLOR]] & "#")

I would advise for next time that when you're given some syntax for a function, copy and paste it somewhere and amend the field names. Right from the start your syntax just wasn't what I wrote.

Thanks vbaInet, problem is I always get a circular reference error, sorry for the late response.. Just couldn't get to a pc.. Somewhere I must have made a big BooBoo.. Just unable to find where..

 

[vbaInet]

You will get a circular reference if the name of the control is the same as the name of the field used in the 3rd argument of the DSum() function.

 

[Marinus]

You will get a circular reference if the name of the control is the same as the name of the field used in the 3rd argument of the DSum() function.

I have tried to solve that with a expression in the query, so there is no circular reference, but honestly.. the problem is that I don't know what I am doing and I might break many rules... I know I might have mistakes in typing over but even after copying and pasting it still wont work. The 99.99 % most probable cause is that I have no clue of the territory I am on. So far I have found out, tables, forms and reports, unfortunately a simple thing like seeing how much money is in cash and what should be left in the till is something I don't get.. Now after reading up on the internet I see that everything you wrote makes sense.. I just can't get it in my head...