Calculating derived fields

[beeky]

I have a form that uses a dataset based on two tables tblWorkRecords and tblWorkDetails. The work details part is a datasheed as I want a one to many form view and data entry. The form is fine but I want to calculate a number of fields derived from stored data. The first is calculating the total number of minutes between two times. I have used the Edit Column option in the datasheet design view and used

=DateDiff("n",[Start],[Finish])[code] in the derived  field. The attached graphic probably demonstrates this better. Clearly I  am doing something wrong can anyone help please? P.S. I previously tred  to use a query in view which did the calutaion but I then had data entry  issues.

[IMG]http://www.box.net/shared/ydvlkmmusu[/IMG]

 

[SOS]

If this is in an Access database, there aren't calculated fields (until you get to Access 2010).

So, it would just need to be a calculation in your text box on your form. You shouldn't be storing that information anyway as it would violate the rules of normalization to do so since you have the parts that make up the calculation stored.

 

[beeky]

No sorry I think I have either entered the wrong forum or I have not explained myself well enough. I am using Visual Basic Express Edition 2008. Also I am not storing the calculations merely using them on the forms and later in a report.

 

[SOS]

I understand that. So you would use code in your form to set the text box (I don't have VB.NET here at work to give the appropriate syntax) to something like

Me.YourTextBoxName = DateDiff(vbIntervalMinutes, Me.YourTextBoxFrom, Me.YourTextBoxTo)

 

[beeky]

No The trouble is that I really don't understand how this VB Express Edition permits changes to columns in datasheet view for subforms in the main form. I have been trying things like "Me.TotalMinutes = DateDiff("n", [Start], [Finish])" but I know this is wrong.

 

[SOS]

I don't understand what you are doing then. In VB.NET there are no "subforms" like in Access and you have to deal with it in ways other than the way you deal with it if you were actually working in Access.

So, you are using Access as your backend, so what does the VB.NET have to do with it? Are you writing a frontend in VB.NET for use with Access or are you trying to manipulate actual Access forms, etc. via VB.NET?

Can you explain further and possibly post some screenshots (upload them here and not on sites like photobucket, etc. as many of us at work do not have access to those photo sharing sites).

 

[beeky]

Sorry I am really not explaining myself very well. The form is a VB form. The fields are based on a dataset. The dataset is built from two tables - tblWorkRecord and tblWorkDetails. The work record (Details from dataset) simply has a client ID, Invoice no and a field for the date an invoice is sent and the date a payment is recieved. The workdetails is a datagrid view of the table tblworkdetails as defined in the dataset. The attached called frmWorkDetails shows the form mentioned above and the EditColumns shows what I am trying to do. The column TotalMinutes is the one I have added.

 

[SOS]

Can you create a derived column in your QUERY that populates the data grid? I would do it there if possible.

 

[beeky]

No I originally tried to develop through a query but it din't work. The relationships were unhappy with what I was trying to do. Unfortunately when creating a dataset in the Express Edittion of VB anyway you don't have the options. You select a table and the fields you want to use but as I deriving the new fields they are not a selection I can make directly from the table.

 

[SOS]

You should be able to set your dataset to a SQL statement instead of a table.

 

[beeky]

I think I am going to try this again. Thanks for your help but I think my brain needs a break for tonight.

 

[SOS]

... I think my brain needs a break for tonight.

I hear you there. I still have ½ a workday to work and I'm already there (brain needs a break).

 

[beeky]

Enjoy, maybe get back tomorrow.