Bill,
I actually solved the date issue by adding ():
Private Sub cmd_CheckDynamics_Click()
Dim strRowSource As String
strRowSource = "SELECT B4602200.CUSTNAME AS Tenant " _
& ", B4602200.BSSI_Lease_Final_ExpDT AS [Lease Expiration DATE] " _
& ", B4602200.LOCNCODE AS [Unit Code] " _
& "FROM B4602200 " _
& "WHERE B4602200.BSSI_Lease_Final_ExpDT > #1/1/1900# " _
& "AND B4602200.BSSI_Lease_Final_ExpDT < DATE() " _
& " AND CUSTNAME = " & Chr(34) & [Applicant Name] & Chr(34)
Dim strRowSource As String
strRowSource = "SELECT B4602200.CUSTNAME AS Tenant " _
& ", B4602200.BSSI_Lease_Final_ExpDT AS [Lease Expiration DATE] " _
& ", B4602200.LOCNCODE AS [Unit Code] " _
& "FROM B4602200 " _
& "WHERE B4602200.BSSI_Lease_Final_ExpDT > #1/1/1900# " _
& "AND B4602200.BSSI_Lease_Final_ExpDT < DATE() " _
& " AND CUSTNAME = " & Chr(34) & [Applicant Name] & Chr(34)
Me.lstLeaseHistory.RowSource = strRowSource
Me.lstLeaseHistory.Visible = True
Me.lstLeaseHistory.Requery
End Sub
End Sub
In a majority of tries this will return a blank record set. What would be the best way to inform the user that there are no records?
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Only those who will risk going too far can possibly find out how far one can go."
On , Art Lorenzini <dbalorenzini@yahoo.com> wrote:
actually put this into my cmdCheckDynamics click event. But now its asking for a "Date" parameter.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Only those who will risk going too far can possibly find out how far one can go."
On Wednesday, May 28, 2014 9:04 AM, "wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Art - A space in the textbox Name?!
This is not really complicated. Using the AfterUpdate of the textbox you can change the listbox RowSource.
Private Sub txtmyDate_AfterUpdate()
Dim strRowSource As String
strRowSource = "SELECT B4602200.CUSTNAME AS Tenant " _
& ", B4602200.BSSI_Lease_Final_ExpDT AS [Lease Expiration DATE] " _
& ", B4602200.LOCNCODE AS [Unit Code] " _
& "FROM B4602200 " _
& "WHERE B4602200.BSSI_Lease_Final_ExpDT > #1/1/1900# " _
& "AND B4602200.BSSI_Lease_Final_ExpDT <DATE " _
& " AND CUSTNAME = " & Chr(34) & [Applicant Name] & Chr(34)
Me.lstLeaseHistory.RowSource = strRowSource
Me.lstLeaseHistory.Visible = True
Me.lstLeaseHistory.Requery
End Sub
Notice I surround the textbox value with Chr(34). That's the ANSI number for double-quotes. I use that in case the cutomer name has an aporstrophe in it like "Joe's Market"
-Bill
---In MS_Access_Professionals@yahoogroups.com, <dbalorenzini@yahoo.com> wrote :
I have a form called ApplicationDetails. On it is a textbox called [Applicant Name]. Also I have a list box called lstLeaseHistory with the following record source:
SELECT B4602200.CUSTNAME AS Tenant, B4602200.BSSI_Lease_Final_ExpDT AS [Lease Expiration Date], B4602200.LOCNCODE AS [Unit Code] FROM B4602200 WHERE (((B4602200.BSSI_Lease_Final_ExpDT)>#1/1/1900# And (B4602200.BSSI_Lease_Final_ExpDT)<Date()));
I am trying to match the value from the Applicant Name to the CUSTNAME field in the query. If there is a match I would like to list the Lease Expiration Date and Unit Number. If no match then return "Not a Previous Tenant".
Also the lstLeaseHistory listbox had its visible property set to false. There is a command button called cmdCheckLeaseHistory. If it is clicked then lstLeaseHistory listbox becomes visible and returns the specific lease information. Now that I am thinking about it maybe the lstbox should be unbound and let the command button handle all the logic. Any idea would be great.
Thank you,
Arthur Lorenzini
Sioux Falls, SD
__._,_.___
Posted by: Art Lorenzini <dbalorenzini@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar