Help the newb

[CowCreek]

Let see if I can splain this. I have a form that has latitude and longitude in degrees, minutes, seconds. Each set of coordinates has their own cell on the table (ie. 29° 56' 56.1"). I also have a textbox and table column that have latitude and longitude in decimal degrees (ie. 29.94891667). I have a formula to convert from D,M,S to DD, but when I put it in the control source for the DD textbox it gives me the answer, but doesn't add it to the table.

I'm trying to make it to when I enter the D,M,S the DD box fills and adds to the table.

 

[pbaldy]

Here's both a caution against doing it and a method to do so should you decide to:

http://allenbrowne.com/casu-14.html

 

[CowCreek]

I see. So what would be the best way to do what I need done? Its a pain in the ass to put in D,M,S and then do the math to convert to DD and put in another column. Its also a pain to put data in for a month and go back a query out the ones that dont have DD and update them. Seems like there would be a way to do it as I go that doesn't have me using a calculator.

Its not like its a per unit price or something like that. D,M,S 3 cells run throught the formula will always yield the answer that i need.

 

[pbaldy]

You must have misread something. This type of code (obviously replacing with your calculation) would put the final value into a textbox, which you would have bound to the table:

Private Sub Quantity_AfterUpdate()
Me.Fee = Round(Me.Quantity * Me.UnitPrice * 0.1, 2)
End Sub

The point about calculated fields is that generally it's not a good idea to store the result of the calculation, just the data necessary to make the calculation. In the example from that link, he's saying to store quantity and unit price, but not the resulting total amount. In your case that means not storing DD, just calculate it as needed from the 3 components. If you still want to, that method above is one way.

 

[CraigDolphin]

Paul is right, but in this instance I can think of one 'valid' reason for storing the result in decimal degrees.

If you use ESRI ArcMap GIS software to link to your database and display the stored locations via an event layer, the source data must be in a table rather than a query. (ArcMap cannot use queries as data sources). And I think that ArcMap requires lat/long as DD format for event layers (?)

If you record your field data in DMS then it is a good thing to store the original format in case you want to verify your data entry against the field forms later. And if you are using the spatial coordinates as source data for GIS, then you have to also store the DD format in a table (using the method Paul linked to).

If you're not using GIS software to link directly to the table then, as Paul noted, you can always calculate the DD format anytime you need it and the general principle to avoid storing calculated data should likely be respected.

In my own work, I work around this by storing only the decimal minutes lat/long data, and then use calculated fields in a make-table query to create the DD format version in a table when needed. (I create the table in a secondary database file to avoid record locking glitches when both GIS and Access software are trying to access the source data).

HTH

 

[CowCreek]

Guess that would have been some helpful info to include. Thats exactly what I'm using it for. All of our records are in Degrees, minutes, seconds and Arc GIS likes Decimal Degrees. I would like to store both, but its a pain to enter both each time I make a new entry (which is often).

So you think the best way to do this and not screw up my data is to scratch the original idea and just do a make table query as I need to import my x-y for GIS. Can put a formula in a make table query to convert the data to Decimal degrees?

 

[ChipperT]

Is there a reason why you need to maintain the Degrees, Minutes and Seconds in your db or can you change your db to simply store decimal degrees?

 

[CowCreek]

thought about doing that, but when I look at the D,M,S I can kinda pick the general location out on our map. Not to mention all the contracter that we work with are set up in D,M,S also.

 

[CraigDolphin]

Can put a formula in a make table query to convert the data to Decimal degrees?

Yes. As I mentioned, you can do this using a calculated field in your make table query.

For example, to calculate the latitude in DD from DMS you might use:
Lat_DD: [Degrees] + ([Minutes]+[Seconds]/60)/60