ACCESS FEED: Re: [MS_AccessPros] Re: VLOOKUP Week videos -- how to do it in Excel ... and in Access

Hi Bill,

> "high-end mic"

I wish! I still use a cheap 19.99 USB mic from Logitech ... I've just gotten
better about conditions and settings when I record ... and I sometimes
filter noise and change volumes. The Excel video was matched to a Modern sound pattern for volume levels. For the Access video, I toned it way
down and used a Classical pattern -- I like that one better too ... way
back when I did not even know about stuff like this. I have used
Goldwave to edit audio since the early 90s, but I barely know anything
about it, just the very basic basics.. Sometimes I find an acorn ;)

> "dulcet tones sound much better than the first video"

not sure what you mean by that ... "first video" -- the Excel one or my first video a couple years ago? I still use a cheap 19.99 USB mic from Logitech ... I've just gotten better about conditions and settings when I record ... and I sometimes filter noise and change volumes. The Excel video was matched to a Modern sound pattern for volume levels. For the Access video, I toned it way down and used a Classical pattern -- I like that one better too ... way back when I did not even know about stuff like this. I have used Goldwave to edit audio since the early 90s, but I barely know anything about it, just the very basic basics.. Sometimes I find an acorn ;)

I also learned a "new" lesson about sound files that I should have known for years ... now I save as WAV even if it
was MP3 to start. MP3 is lossy like JPGs.

The Access video was just a
stream from my sleepy state. I am creating
the captions now ... I did write a script that is nowhere close to what I said so the captions are taking a lot longer to create since I have to transcribe everything ... and I just can't bring myself to write what I actually said in several places, I was really tired.

> code to calculate?

here is all the code:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function GetDistance(pLat1 As Double, pLng1 As Double _
   , pLat2 As Double, pLng2 As Double _
   , Optional pWhich As Integer = 1 _
   ) As Double
'12-13-08, 12-22 Crystal
   ' calculates distance between 2 points of Latitude and Longitude
   ' in Statute Miles, Kilometers, or Nautical Miles
   ' crystal strive4peace2008 at yahoo.com

   'PARAMETERS
   ' pLat1 is Latitude of the first point in decimal degrees
   ' pLng1 is Longitude of the first point in decimal degrees
   ' pLat2 is Latitude of the second point in decimal degrees
   ' pLng2 is Longitude of the second point in decimal degrees

   On Error Resume Next
   Dim EarthRadius As Double

   Select Case pWhich
   Case 2:
      EarthRadius = 6378.7
   Case 3:
      EarthRadius = 3437.74677
   Case Else
      EarthRadius = 3963
   End Select

   ' Radius of Earth:
   ' 1 3963.0 (statute miles)
   ' 2 6378.7 (kilometers)
   ' 3 3437.74677 (nautical miles)
   ' to convert degrees to radians, divide by 180/pi, which is 57.2958
   GetDistance = 0

   Dim X As Double

    X = (Sin(pLat1 / 57.2958) * Sin(pLat2 / 57.2958)) _
      + (Cos(pLat1 / 57.2958) * Cos(pLat2 / 57.2958) * Cos(pLng2 / 57.2958 - pLng1 / 57.2958))

   GetDistance = EarthRadius * Atn(Sqr(1 - X ^ 2) / X)
End Function

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'120331
   Dim ctl As Control
   Select Case True
      Case Me.RowCounter Mod 2 = 0 'white or light
         For Each ctl In Me.Detail.Controls
            If ctl.Tag = "orange" Then
               ctl.BackColor = RGB(255, 255, 255)
            ElseIf ctl.Tag = "aqua" Then
               ctl.BackColor = RGB(240, 250, 250)
            End If
         Next ctl

      Case Me.RowCounter Mod 4 = 1 'medium
         For Each ctl In Me.Detail.Controls
            If ctl.Tag = "orange" Then
               ctl.BackColor = RGB(250, 200, 170)
            ElseIf ctl.Tag = "aqua" Then
               ctl.BackColor = RGB(200, 220, 220)
            End If
         Next ctl

      Case Else 'light
         For Each ctl In Me.Detail.Controls
            If ctl.Tag = "orange" Then
               ctl.BackColor = RGB(255, 230, 220)
            ElseIf ctl.Tag = "aqua" Then
               ctl.BackColor = RGB(220, 240, 240)
            End If
         Next ctl

      End Select

      For Each ctl In Me.Detail.Controls
         If ctl.Value <= 50 Then
            ctl.ForeColor = RGB(255, 0, 0)
            ctl.FontBold = True
         Else
            ctl.ForeColor = RGB(0, 0, 0)
            ctl.FontBold = False
         End If
      Next ctl
End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal

http://www.YouTube.com/LearnAccessByCrystal

*
   (: have an awesome day :)
*

________________________________
From: Bill Mosca <wrmosca@comcast.net>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, April 3, 2012 3:30 PM
Subject: [MS_AccessPros] Re: VLOOKUP Week videos -- how to do it in Excel ... and in Access

Crystal

What about your code to calculate? I'd love a copy of that.

By the bye, you must have bought a high-end mic. Your dulcet tones sound much better than the first video. :O)

Bill

--- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@...> wrote:
>
> Hi Bill,
>
>
> thank you :)
>
>
> I did this video twice -- first time I used the wrong resolution to record, grrr! Did not figure that out till I was mixing the audio (which the first time was done separately to match the video so I redid that too). Then I recorded everything again and did audio and video in one pass ... hard to do that and follow a script so my first query was done differently. Running the query was slower the second time (I cut the processing time from the video). I have been wondering why ... I think I know -- the query performance of qAirportsNY will be faster with an outer join ... have to test this -- and if true, maybe I'll make another video about it :)
>
> > "sample DB"
>
> not yet ... because of the above issue ... all the steps are there though :)
>
>
> Warm Regards,
> Crystal
>
> Learn Access on YouTube
> http://www.YouTube.com/LearnAccessByCrystal
>
> *
>    (: have an awesome day :)
> *
>
>
>
> ________________________________
> From: Bill Mosca <wrmosca@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Tuesday, April 3, 2012 2:10 PM
> Subject: [MS_AccessPros] Re: VLOOKUP Week videos -- how to do it in Excel ... and in Access
>
> Hi Crystal
>
> Great video with a good example of Cartesian result sets. Where can we get your code? Did you upload a sample DB?
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile/Bill.Mosca
>
>
> --- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@> wrote:
> >
> >
> > Last week was VLOOKUP Week! ... a tradition started by Bill "MrExcel" Jelen, who has a YouTube channel called BJELE123 with great videos for learning Excel. When Bill asked me to do a VLOOKUP video, I was honored as I usually make videos for Access and Windows Phone development. It was fun!
> >
> > This video calculates distance between coordinates using VLOOKUP to get Latitude and Longitude from a table of Zip Codes, which is available as a free download on my website.
> >
> > There is also Concatenation, Errors (of course) and how to fix them, Relative and Absolute References, Range Names, Transposing, VBA, Table Styles, Formatting, and Conditional Formatting.
> >
> > Excel VLOOKUP to Calculate Distances using Latitude & Longitude, by Crystal (cc) VLOOKUP Week
> > http://www.youtube.com/watch?v=bc92JR2OPss&feature=BFa&list=PL7C75A052F80E19B7
> >
> > Now, we build the same thing in Access ...
> >
> > Import tables from another database, DLookup, DMin, Query Critera and Calculated fields, VBA, Crosstab, Report Design, Align controls, Change widths, Format numbers, Page Layout, Detail Section, change Colors based on Row.
> >
> > Convert VLOOKUP equations to Access, by Crystal for VLOOKUP Week
> > http://www.youtube.com/watch?v=ED_Wd4w9PLk&feature=BFa&list=PL7C75A052F80E19B7
> >
> >
> > Warm Regards,
> > Crystal
> >
> > *
> >    (: have an awesome day :)
> > *
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
> [Non-text portions of this message have been removed]
>

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]