query and search by name

[jmore]

I am trying to set up a query to search by name and by dates....I am able to let the users search by dates by letting the users enter in the dates they want to search by - the problem I'm having is the user can't remember the names they want to search by, so I can't have them search by name,is there a way for me to use a list box or something to have them choose the names they want to search by?

 

[czampa]

Have you tried to use the Show Values criteria in the Query?

 

[vskoo]

In order to do a search by name query from an EIS, the user
would need to have an empty text field to enter in the search string.

Here is the sample code that I used:

TRQuery = ActiveDocument.Sections["Task Report Query"];
TRQuery.Limits.RemoveAll();

// Customer Name
if (CustomerName.Text != "&quot
{
var lcustomer = ActiveDocument.Sections["Task Report Query"].Limits.CreateLimit("batch_report_data.CORPORATE_NAME"
lcustomer.Operator = bqLimitOperatorLike;
var sCustomer = CustomerName.Text + "%";
lcustomer.CustomValues.Add(sCustomer);
lcustomer.SelectedValues.Add(sCustomer);
TRQuery.Limits.Add(lcustomer);
}

In this scenario, the user entered in the customer name and clicked on a submit button, which executed this script.
The script activated an existing query and created a new limit string using the user input. I hope this helps.

 

[jmore]

I need help with a query - I need to create a query that will use the current date and only pull records for the previous week - I will even take help with a query that will select the specified week . I need the week becasue once I have selected the week, I will then need to use a crosstab query. I cannot use the {start date], [end date] in the query because the crosstab won't recognize it...any ideas on how to do this?

 

[vskoo]

Similar to the other solution above:

Here is the code that I used to process a date query

// Ready Time Begin
var sMM1, sDD1, YYYY1, sDate1;
var bdatesOK = true;

// Check if Received Date selected
if ((DDBeginDateYear.SelectedIndex > 1) || (DDBeginDateMonth.SelectedIndex > 1) ||
(DDBeginDateDay.SelectedIndex > 1) )
{
// Check if all Received Dates selected
if ((DDBeginDateYear.SelectedIndex > 1) && (DDBeginDateMonth.SelectedIndex > 1) &&
(DDBeginDateDay.SelectedIndex > 1) )
{
// Set Month
if ((DDBeginDateMonth.SelectedIndex - 1) < 10)
{
sMM1 = &quot;0&quot; + (DDBeginDateMonth.SelectedIndex - 1);
}
else
{
sMM1 = DDBeginDateMonth.SelectedIndex - 1;
}
// Set Day
if ((DDBeginDateDay.SelectedIndex - 1) < 10)
{
sDD1 = &quot;0&quot; + (DDBeginDateDay.SelectedIndex - 1);
}
else
{
sDD1 = DDBeginDateDay.SelectedIndex - 1;
}
YYYY1 = DDBeginDateYear.Item(DDBeginDateYear.SelectedIndex);
var sDate1;
sDate1 = sMM1 + &quot;/&quot; + sDD1 + &quot;/&quot; + YYYY1;
var lDate1 = ActiveDocument.Sections[&quot;PO Package SubQuery&quot;].Limits.CreateLimit(&quot;batch_report_data.ORD_INIT_DT&quot;
lDate1.Operator = bqLimitOperatorGreaterThanOrEqual;
lDate1.CustomValues.Add(sDate1);
lDate1.SelectedValues.Add(sDate1);
POQuery.Limits.Add(lDate1);

var lDate11 = ActiveDocument.Sections[&quot;Package Origination Product SubQuery&quot;].Limits.CreateLimit(&quot;batch_report_data.ORD_INIT_DT&quot;
lDate11.Operator = bqLimitOperatorGreaterThanOrEqual;
lDate11.CustomValues.Add(sDate1);
lDate11.SelectedValues.Add(sDate1);
POQuery2.Limits.Add(lDate11);

// Add sDate1 to Results page
ActiveDocument.Sections[&quot;Package Origination Results&quot;].Shapes[&quot;DateRange&quot;].Text=sDate1;
}
else
{
Application.Alert(&quot;Invalid Begin Date entered&quot;
bdatesOK = false;
}
}
else
{
Application.Alert(&quot;Begin Date required&quot;
bdatesOK = false;
}


// Ready Time End
var MM1e, DD1e, YYYY1e;

// Check if Received Date selected
if ((DDEndDateYear.SelectedIndex > 1) || (DDEndDateMonth.SelectedIndex > 1) ||
(DDEndDateDay.SelectedIndex > 1) )
{
if ((DDEndDateYear.SelectedIndex > 1) && (DDEndDateMonth.SelectedIndex > 1) &&
(DDEndDateDay.SelectedIndex > 1) )
{
// Set Month
if ((DDEndDateMonth.SelectedIndex - 1) < 10)
{
MM1e = &quot;0&quot; + (DDEndDateMonth.SelectedIndex - 1);
}
else
{
MM1e = DDEndDateMonth.SelectedIndex - 1;
}
// Set Day
if ((DDEndDateDay.SelectedIndex - 1) < 10)
{
DD1e = &quot;0&quot; + (DDEndDateDay.SelectedIndex - 1);
}
else
{
DD1e = DDEndDateDay.SelectedIndex - 1;
}
YYYY1e = DDEndDateYear.Item(DDEndDateYear.SelectedIndex);
var sDate1e;
sDate1e = MM1e + &quot;/&quot; + DD1e + &quot;/&quot; + YYYY1e;
var lDate1e = ActiveDocument.Sections[&quot;PO Package SubQuery&quot;].Limits.CreateLimit(&quot;batch_report_data.ORD_INIT_DT&quot;
lDate1e.Operator = bqLimitOperatorLessThanOrEqual;
lDate1e.CustomValues.Add(sDate1e);
lDate1e.SelectedValues.Add(sDate1e);
POQuery.Limits.Add(lDate1e);

var lDate11e = ActiveDocument.Sections[&quot;Package Origination Product SubQuery&quot;].Limits.CreateLimit(&quot;batch_report_data.ORD_INIT_DT&quot;
lDate11e.Operator = bqLimitOperatorLessThanOrEqual;
lDate11e.CustomValues.Add(sDate1e);
lDate11e.SelectedValues.Add(sDate1e);
POQuery2.Limits.Add(lDate11e);

// Add sDate1e to Results page
ActiveDocument.Sections[&quot;Package Origination Results&quot;].Shapes[&quot;DateRange2&quot;].Text= &quot;- &quot; + sDate1e;

if (YYYY1 > YYYY1e)
{
Application.Alert(&quot;End Date must be after Received Begin Date1&quot;
bdatesOK = false;
}
else if ((YYYY1 == YYYY1e) && (sMM1 > MM1e))
{
Application.Alert(&quot;End Date must be after Received Begin Date2&quot;
bdatesOK = false;
}
else if ((YYYY1 == YYYY1e) && (sMM1 == MM1e) && (sDD1 >= DD1e))
{
Application.Alert(&quot;End Date must be after Received Begin Date3&quot;
bdatesOK = false;
}
}
}

I hope this helps